Бывают ситуации, когда необходимо отрезать/ удалить строго определенное количество символов в тексте справа или слева. Например, когда вы имеете список адресов, и вначале каждого адреса стоит шестизначный индекс. Нужно убрать почтовые индексы из списка, но через замену их не уберешь, так как они имеют совершенно разные значения и не находятся опцией замены.
Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).
Рассмотрим их применение.
Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.
Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.
Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.
Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:
ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).
Где …*¹- адрес ячейки, из которой берем текст.
- Формулы удаления N символов в начале и конце строк
- Удалить символы в начале ячейки (слева): функция надстройки !SEMTools
- Удалить символы в конце ячейки (справа): функция надстройки !SEMTools
- Удалить слова и символы по другим условиям
Как удалить первый/последний символ или несколько первых/последних символов в ячейке Excel? Узнайте ответ из этой статьи.
Примечание автора: речь пойдет о ситуациях, когда количество символов определено конкретным числом — один, два, три и т.д. Тому, как удалить текст до определенного символа, посвящена отдельная статья.
Для составления таких формул понадобятся функции ЛЕВСИМВ, ПРАВСИМВ и ДЛСТР. Первые две извлекают из текста указанное количество символов слева и справа соответственно, а ДЛСТР возвращает количество символов в тексте ячейки, к которой обращается.
Чтобы удалить из ячейки N символов слева, можно взять справа количество символов, равное разнице длины строки в символах (ДЛСТР) и N:
=ПРАВСИМВ(A1;ДЛСТР(A1)-N)
А чтобы удалить из ячейки N символов справа, наоборот, извлекаем слева длину строки минус N:
Удалить символы в начале ячейки (слева): функция надстройки !SEMTools
Формулы выше требуют создания дополнительного столбца. Чтобы сэкономить время, если исходные данные не нужны, я разработал функции !SEMTools, удаляющие символы прямо на месте.
Если не уверены до конца в том, что делаете, рекомендуется все же перестраховываться, создавая дополнительный столбец.
Удалить символы в конце ячейки (справа): функция надстройки !SEMTools
Процедура потребуется, когда у вас есть массив фраз, в котором нужно обрезать последние буквы в конце строки, и вы уверены на 100% в их количестве.
Выделите диапазон, затем на панели !SEMTools в меню «Удалить» выберите «Символы -> обрезать -> с конца ячейки». В появившемся диалоговом окошке вбейте количество символов, которое хотите удалить, и нажмите «ОК».
Удалить слова и символы по другим условиям
Надеюсь, на этой странице вы нашли то, что искали, и смогли решить задачу удаления последних или первых символов в ячейках.
Вас наверняка могут заинтересовать похожие операции, смотрите подробнее:
- Взять первые N символов ячейки в Excel;
- Удалить лишние пробелы в Excel;
- Удалить латиницу;
- Удалить все символы, кроме цифр в Excel;
- Удалить все символы, кроме букв и цифр;
- Удалить первое слово или первые N слов в ячейках;
- Регулярные выражения в Excel.
Хотите так же быстро удалять символы в Excel без использования формул?
В надстройке !SemTools есть этот и ещё более 500 похожих полезных инструментов по обработке текста. Сделайте апгрейд вашего Excel!
На чтение 5 мин Просмотров 8.2к. Опубликовано 12.05.2022
Часто бывает такое, что нужно удалить один или несколько символов из начала каждой ячейки.
Допустим у нас есть первый столбик, в котором лежат значения буква+цифры, нам нужно обрезать их таким образом, чтобы получились только цифры. Как на картинке ниже:
На самом деле, есть несколько вариантов, как можно это сделать.
Итак, давайте начнем!
Содержание
- С помощью функции ЗАМЕНА
- Копируем из ячейки все, кроме первого символа
- С помощью функции «Текст по столбцам»
- С помощью автозаполнения
- С помощью Visual Basic
С помощью функции ЗАМЕНА
Самый распространенный способ — использовать функцию ЗАМЕНА. Она довольно простая и в то же время полезная.
Для указанного выше примера формула функции принимает такой вид:
=ЗАМЕНА(A2;1;1;"")Что она делает?
Просто заменяет первый символ в строке на пустое место.
С помощью неё можно удалить любое количество символов с начала строки, например удалить два символа можно так:
=ЗАМЕНА(A1;1;2;"")Копируем из ячейки все, кроме первого символа
Можно сделать и так, в этом случае формула будет немного сложней, но результата мы добьемся.
Разница в том, что в первом случае мы удаляли первый символ из строки, а в этом случае — мы просто разделяем строку и переносим нужную её часть в другую ячейку.
Допустим, у нас тот же столбик, который нужно обработать:
В этой ситуации формула принимает такой вид:
=ПРАВСИМВ(A1;ДЛСТР(A1)-1)
Функция ДЛСТР получает длину всей строки, а далее из длины убавляется 1 (потому что 1 символ мы хотим «пропустить») и все это отдается в обработку функции ПРАВСИМВ.
С помощью функции «Текст по столбцам»
Прошлые методы простые, но неудобны тем, что для них нужен отдельный столбец, так сказать, «технический» (для расчетов).
Если, например, вам нужно получить результат в тех же ячейках, которые мы обрабатываем, грубо говоря, перезаписать их, то вам поможет этот метод.
Допустим, у нас тот же столбик:
Пошаговая инструкция:
- Выделите ячейки и щелкните на «Данные»;
- Далее «Текст по столбцам»;
- В открывшемся окошке:
- В первом шаге используйте вторую опцию (как на картинке);
- Во втором шаге установите стрелку на том делении, сколько символов вы хотите «отрезать»;
- И в последнем шаге, выберите «пропустить столбец»;
- Подтвердите.
Таким образом, первый символ был удален.
Как это работает?
Довольно просто: вы указали функции сколько символов нужно «отрезать» от начала строки, а также вы указали пропустить столбец. Это значит что данные, после обработки, будут записаны в тот же столбик, где они изначально были.
С помощью автозаполнения
Эта функция — одна из самых удобных функций в Excel. Она автоматически пытается угадать значения для следующих ячеек, если вы заполнили первую (одну или две).
Давайте посмотрим как это работает.
Допустим, у нас тот же столбик:
Воспользуемся этой функцией
Пошаговая инструкция:
- В следующем столбике, впишите значение, которое должно быть после преобразования (в нашем случае 271);
- А также, по аналогии, укажите значение для второй ячейки. На самом деле, функция автозаполнения уже поняла логику и предлагает нам заполнить всю оставшуюся часть;
- Теперь выделите обе ячейки с обработанными данными;
- Наведите курсор на правую нижнюю часть выделения и потяните её вниз;
- Готово!
Просто не правда ли?
Это удобная функция, но с ней нужно быть осторожным. Если она неправильно поймет логику вашего заполнения — могут быть ошибки.
С помощью Visual Basic
Ну и как обычно, напоследок, рассмотрим как можно сделать это с помощью Visual Basic.
Я дам вам код функции для такого рода задачи, вы сможете модифицировать его.
Также, как и в случае использования функции «Текст по столбцам» в этом методе нам не нужен дополнительный столбик для расчетов.
Обратите внимание, что если строк, которые нужно обработать у вас очень много, выполнение этого кода может замедлить работу компьютера на какое-то время.
Этот код удаляет первый символ из каждой ячейки:
For Each cell In Selection: cell.Value = Right(cell.Value, Len(cell.Value) - 1): Next cellКак его использовать?
Пошаговая инструкция:
- Выделите диапазон ячеек, в которых нужно удалить первый символ
- Откройте просмотр кода (правой кнопкой на имя листа -> «Просмотр кода»);
- Далее щелкните на «View» -> «Immediate Window» (или CTRL + G);
- Поместите в окно наш код;
- Обязательно наведите мышь в конец кода;
- И просто подтвердите (нажав ENTER);
Готово, первый символ удален.
Что же делает этот код?
For Each cell In Selection: cell.Value = Right(cell.Value, Len(cell.Value) - 1): Next cell Итак, здесь используется цикл, который копирует и перезаписывает каждую ячейку отдельно.
В общем то и все! Мы рассмотрели несколько удобных и практичных методов удаления первого символа из каждой ячейки.
Я надеюсь эта статья была полезна для вас! Потому что даже если какой-то способ вам не по душе, вы всегда можете использовать несколько других.
При выгрузке из учетной программы, при скачивании данных из интернета или, если файл заполняется неопытным пользователем, случается, что в ячейках остаются лишние символы, пробелы, точки, знаки переноса строки и т.п. Как быстро и просто удалить любой символ в ячейке Excel?
Самая распространенная ошибка, когда Excel не воспринимает текст как число. Об этом можно читать здесь. Способов как удалить символ в ячейке много, постараюсь рассказать о каждом подробнее.
Содержание
- 1. Найти и заменить символ в ячейке Excel
- 2. Удалить символ в ячейке Excel формулой
- 3. =СЖПРОБЕЛЫ()
- 4. Удалить первый символ
- 5. Функция =ПЕЧСИМВ()
- Похожие статьи
1. Найти и заменить символ в ячейке Excel
Если вкратце, то существует специальный инструмент во всех продуктах MS Office. Вызывается клавишами Ctrl+F (найти) или Ctrl+H (заменить). В первое поле можно вписать символ, который нужно удалить, во второе ничего не вписывайте.
Подробнее об этом инструменте написано в этой статье
2. Удалить символ в ячейке Excel формулой
Удалить любой символ можно прекрасной функцией =ПОДСТАВИТЬ().
Эта функция имеет следующую конструкцию =ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)
текст — здесь указывается ячейка, где записан текст для изменения
старый_текст — что меняем
новый_текст — на что меняем
номер_вхождения — является необязательным. Он предписывает Excel заменить только символы, начиная с определенного номера. Интересное дополнение
Представим, что вам нужно заменить все точки на запятые, получится формула:
=ПОДСТАВИТЬ(A1;".";",")
3. =СЖПРОБЕЛЫ()
Часто пробелы не видны невооруженным глазом («Иванов Иван «), нужно писать дополнительную проверку, чтобы найти их, в общем, предпринимать дополнительные действия. Проще всего воспользоваться формулой =СЖПРОБЕЛЫ(). Она убирает все задвоенные пробелы, а так же лишние пробелы в конце и начале текста в ячейке (станет «Иванов Иван») .
Но! Будьте внимательнее — формула удаляет стандартные пробелы. Если вы к примеру копировали текст с сайта — он может содержать символы похожие на пробелы, но функция =СЖПРОБЕЛЫ() не будет их удалять. В таком случае придется пользоваться возможностью Найти и заменить.
Но в 95% случаев сжпробелы помогает на «отлично».
Удаление пробелов я даже вынес в отдельную статью
4. Удалить первый символ
Часто необходимо удалить первый, несколько последних символов и так далее. Как это сделать быстро и не вручную? Воспользуйтесь функцией ПСТР.
Очень подробно об этом написано здесь.
Например, у нас в ячейках задан порядковый номер сотрудника. Нужно удалить первые символы до точки и пробел.
Формула будет выглядеть следующим образом:
=ПСТР(A:A;ПОИСК(".";A:A)+2;ДЛСТР(A:A)-ПОИСК(".";A:A))
Так же можно разделить текст по столбцам и разнести их в разные ячейки.
Пример
5. Функция =ПЕЧСИМВ()
Функция с одним аргументом очень похожа на СЖПРОБЕЛЫ, но она удаляет непечатные символы в строке, такие как перенос строки, знаки абзаца, какие-нибудь квадратики и т.п. (первые 32 непечатаемых знаков в 7-разрядном коде ASCII). Особенно полезна она, если нужно удалить перенос строки — мне всегда помогает.
Внимание! ПЕЧСИМВ способна удалить большинство, но не все лишние символы.
Skip to content
В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое.
Среди множества различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВСИМВ — одна из наиболее широко применяемых. Как следует из названия, она позволяет извлекать определенное количество знаков, начиная с левой стороны текста. Однако она способна на гораздо большее, чем такая простая операция. В этом руководстве вы найдете несколько базовых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать ЛЕВСИМВ далеко за пределами ее базового применения.
- Правила синтаксиса.
- Как получить подстроку слева от определенного символа?
- Удаляем последние N символов.
- Как заставить ЛЕВСИМВ вернуть число, а не текст.
- Почему не работает?
Cинтаксис.
Функция ЛЕВСИМВ в Excel возвращает указанное количество символов (подстроку) от начала содержимого ячейки.
Синтаксис следующий:
ЛЕВСИМВ (текст; [колич_знаков])
Где:
- Текст (обязательно) — это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) — количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Например, чтобы извлечь первые 6 символов из A2, запишите такое выражение:
=ЛЕВСИМВ(A2; 6)
На следующем скриншоте показан результат:
Важное замечание! ЛЕВСИМВ относится к категории текстовых функций, поэтому результатом её всегда является текстовая строка, даже если исходное значение, из которого вы извлекаете цифры, является числом. Если вы работаете с числовым набором данных и хотите, чтобы было извлечено именно число, применяйте ее вместе с функцией ЗНАЧЕН, как показано в одном из примеров ниже.
Что еще можно делать, помимо извлечения текста слева? В следующих примерах показано, как можно применять её в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом.
В некоторых случаях может потребоваться извлечь часть текста, который предшествует определенному знаку. Например, вы можете извлечь имена из столбца ФИО или получить коды стран из колонки с телефонными номерами. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому вы не можете просто указать точное число сколько знаков отделить, как мы сделали в приведенном выше примере.
Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .
Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(» «; A2)). А теперь вы вставляете это выражение в ЛЕВСИМВ:
=ЛЕВСИМВ(A2; ПОИСК(» «; A2))
Чтобы еще немного улучшить результат, избавьтесь от конечного пробела, вычтя 1 из результата поиска. Ведь невидимые конечные пробелы могут вызвать множество проблем, особенно если вы планируете использовать извлеченные имена в других вычислениях:
=ЛЕВСИМВ(A2; ПОИСК(» «; A2)-1)
Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса («-«), а не пробела:
=ЛЕВСИМВ(A2; ПОИСК(«-«; A2)-1)
Думаю, вы поняли, что можете брать эту универсальную формулу, чтобы получить подстроку, которая предшествует любому другому знаку:
ЛЕВСИМВ( строка ; ПОИСК( символ ; строка ) — 1)
Как удалить последние N символов.
Вы уже знаете, отрезать кусочек от начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов из конца строки и перенести оставшееся в другую ячейку. Для этого можно также применять функцию ЛЕВСИМВ в сочетании с ДЛСТР , например:
ЛЕВСИМВ ( текст ; ДЛСТР( текст ) — число_символов_для_удаления )
Это работает с такой логикой: ДЛСТР получает общее количество символов в ячейке, затем вы вычитаете количество ненужных знаков из общей длины, а ЛЕВСИМВ возвращает оставшееся.
Например, чтобы удалить последние 7 знаков из текста в A2, запишите такое выражение:
=ЛЕВСИМВ(A2; ДЛСТР(A2)-11)
Как показано на скриншоте, формула успешно отсекает слово «продукты» (8 букв, разделитель и 2 пробела) из текстовых значений в столбце A.
При этом взять на вооружение способ, рассмотренный нами в предыдущем примере, будет очень сложно, так как все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы уже знаете, ЛЕВСИМВ в Эксель всегда возвращает текст, даже если вы извлекаете несколько первых цифр из ячейки. Для вас это означает, что вы не сможете использовать эти результаты в вычислениях или в других функциях Excel, которые работают с числами.
Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь символы перед разделителем “-” из A2 и преобразовать результат в число, можно сделать так:
=ЗНАЧЕН(ЛЕВСИМВ(A2;ПОИСК(«-«;A2;1)-1))
Результат будет выглядеть примерно так:
Как вы можете видеть на скриншоте выше, числа в столбце B, полученные с помощью преобразования, автоматически выровнены по правому краю в ячейках, в отличие от текста с выравниванием по левому краю в столбце A. Поскольку Эксель распознает итоговые данные как числа, вы можете суммировать и усреднять эти значения, находить минимальное и максимальное значение и выполнять любые другие вычисления с ними.
Это лишь некоторые из множества возможных вариантов использования ЛЕВСИМВ в Excel.
Дополнительные примеры формул ЛЕВСИМВ можно найти на следующих ресурсах:
- Разделить строку запятой, двоеточием, косой чертой, тире или другим разделителем
- Как разбить текстовую строку вида «число + текст» по столбцам
- Подсчитайте количество символов до или после данного символа
Не работает ЛЕВСИМВ — причины и решения
Если ЛЕВСИМВ не работает на ваших листах должным образом, это, скорее всего, связано с одной из причин, которые мы перечислим ниже.
1. Аргумент «количество знаков» меньше нуля
Если ваша формула возвращает ошибку #ЗНАЧ!, то первое, что вам нужно проверить, — это значение аргумента количество_знаков. Если вы видите отрицательное число, просто удалите знак минус, и ошибка исчезнет (конечно, очень маловероятно, что кто-то намеренно поставит отрицательное число, но человек может ошибиться 
Чаще всего ошибка #ЗНАЧ! возникает, когда этот аргумент получен в результате вычислений, а не записан вручную. В этом случае скопируйте это вычисление в другую ячейку или выберите его в строке формул и нажмите F9, чтобы увидеть результат ее работы. Если значение меньше 0, проверьте на наличие ошибок.
Чтобы лучше проиллюстрировать эту мысль, возьмем формулу, которую мы записали в первом примере для извлечения телефонных кодов страны:
ЛЕВСИМВ(A2; ПОИСК(«-«; A2)-1)
Как вы помните, функция ПОИСК в наших примерах вычисляет позицию первого дефиса в исходной строке, из которой мы затем вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, на -11, Эксель выдаст ошибку #ЗНАЧ!, потому что нельзя извлечь отрицательное количество букв и цифр:
2. Начальные пробелы в исходном тексте
Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, довольно часто такие неприятные сюрпризы попадаются в самом начале текста. И вы вряд ли заметите, что они там есть, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от ведущих пробелов на листах, воспользуйтесь СЖПРОБЕЛЫ (TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать ЛЕВСИМВ для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как числа, представляющие количество дней с 1 января 1900 года. То, что вы видите в ячейке, это просто визуальное представление даты. Ее отображение можно легко изменить, применив другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день с помощью выражения ЛЕВСИМВ(A1;2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое представляет 15 июля 2020г. во внутренней системе Эксель.
Чтобы извлечь определенную часть даты, возьмите одну из следующих функций: ДЕНЬ(), МЕСЯЦ() или ГОД().
Если же ваши даты вводятся в виде текстовых строк, то ЛЕВСИМВ будет работать без проблем, как показано в правой части скриншота:
Вот как можно использовать функцию ЛЕВСИМВ в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel вы можете выполнить при помощи специального инструмента работы с текстом, включённого в надстройку Ultimate Suite. Вот только некоторые из этих возможностей: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать буквы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить по ячейкам, извлечь отдельные слова, найти дубликаты слов.
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… 
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… 
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… 
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… 
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… 
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… 
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…