Удалить первый или последний символы (слева или справа)
При работе с неструктурированными текстовыми данными на ваших листах вам часто приходится анализировать их, чтобы получить соответствующую информацию. Эта статья научит вас нескольким простым способам удаления любого количества символов с левой или правой стороны текстовой строки.
Как удалить символы слева в Excel
Удаление первых символов из строки — одна из самых распространенных задач в Excel, и ее можно выполнить с помощью 3 разных формул.
Удалить первый символ в Excel
Чтобы удалить первый символ из строки, вы можете использовать либо функцию REPLACE, либо комбинацию функций RIGHT и LEN.
ЗАМЕНЯТЬ(нить1, 1, “”)
Здесь мы просто берем 1 символ из первой позиции и заменяем его пустой строкой (“”).
ПРАВИЛЬНО(нитьЛЕН(нить) – 1)
В этой формуле мы используем функцию ДЛСТР, чтобы вычислить общую длину строки и вычесть из нее 1 символ. Разница передается ПРАВИЛЬНО, поэтому она извлекает столько символов из конца строки.
Например, чтобы удалить первый символ из ячейки A2, формулы выглядят следующим образом:
=ЗАМЕНИТЬ(A2, 1, 1, “”)
=ВПРАВО(A2, ДЛИН(A2) – 1)
Удалить символы слева
Чтобы удалить начальные символы с левой стороны строки, вы также используете функции REPLACE или RIGHT и LEN, но каждый раз указываете, сколько символов вы хотите удалить:
ЗАМЕНЯТЬ(нить1, num_chars“”)
Или же
ПРАВИЛЬНО(нитьЛЕН(нить) – num_chars)
Например, чтобы удалить первые 2 символа из строки в A2, формулы таковы:
=ЗАМЕНИТЬ(A2, 1, 2, “”)
=ВПРАВО(A2, ДЛИН(A2) – 2)
Чтобы удалить первые 3 символа, формулы принимают следующий вид:
=ЗАМЕНИТЬ(A2, 1, 3, “”)
=ВПРАВО(A2, ДЛИН(A2) – 3)
На приведенном ниже снимке экрана показана формула REPLACE в действии. С RIGHT LEN результаты будут точно такими же.
Пользовательская функция для удаления первых n символов
Если вы не возражаете против использования VBA в своих рабочих листах, вы можете создать свою собственную определяемую пользователем функцию для удаления символов с начала строки с именем Ремовефирстчарс. Код функции такой же простой:
Функция RemoveFirstChars(str As String, num_chars As Long) RemoveFirstChars = Right(str, Len(str) – num_chars) End Function
Как только код будет вставлен в вашу книгу (подробные инструкции здесь), вы можете удалить первые n символов из заданной ячейки, используя эту компактную и интуитивно понятную формулу:
RemoveFirstChars(строка, num_chars)
Например, чтобы удалить первый символ из строки в A2, формула в B2 будет следующей:
= Удалить первые символы (A2, 1)
Чтобы удалить первые два символа из A3, используйте формулу в B3:
= Удалить первые символы (A4, 2)
Чтобы удалить первые три символа из A4, используйте формулу в B4:
= Удалить первые символы (A4, 3)
Подробнее об использовании пользовательских функций в Excel.
Как убрать символы справа
Чтобы удалить символы с правой стороны строки, вы также можете использовать встроенные функции или создать свою собственную.
Удалить последний символ в Excel
Чтобы удалить последний символ в ячейке, используйте общую формулу:
ОСТАВИЛ(нитьЛЕН(нить) – 1)
В этой формуле вы вычитаете 1 из общей длины строки и передаете разницу функции ВЛЕВО, чтобы она извлекла столько символов из начала строки.
Например, чтобы удалить последний символ из ячейки A2, используйте формулу в ячейке B2:
=ВЛЕВО(A2, ДЛИН(A2) – 1)
Удалить символы справа
Чтобы удалить заданное количество символов с конца ячейки, используется общая формула:
ОСТАВИЛ(нитьЛЕН(нить) – num_chars)
Логика та же, что и в формуле выше, а ниже пара примеров.
Чтобы удалить последние 3 символа, используйте 3 для num_chars:
=ВЛЕВО(A2, ДЛИН(A2) – 3)
Чтобы удалить последние 5 символов, введите 5 для num_chars:
=ВЛЕВО(A2, ДЛИН(A2) – 5)
Пользовательская функция для удаления последних n символов в Excel
Если вы хотите иметь собственную функцию для удаления любого количества символов справа, добавьте этот код VBA в свою книгу:
Функция RemoveLastChars(str As String, num_chars As Long) RemoveLastChars = Left(str, Len(str) – num_chars) End Function
Функция названа Удалить ЛастЧарс и его синтаксис вряд ли нуждается в объяснении:
RemoveLastChars(строка, num_chars)
Чтобы проверить это в полевых условиях, давайте избавимся от последнего символа в A2:
= Удалить последние символы (A2, 1)
Кроме того, мы удалим последние 2 символа с правой стороны строки в A3:
= Удалить последние символы (A3, 2)
Чтобы удалить последние 3 символа из ячейки A4, используйте формулу:
= Удалить последние символы (A4, 3)
Как вы можете видеть на скриншоте ниже, наша пользовательская функция работает блестяще!
Как удалить символы справа и слева одновременно
В ситуации, когда вам нужно стереть символы с обеих сторон строки, вы можете либо запустить обе приведенные выше формулы последовательно, либо оптимизировать работу с помощью функции MID.
СРЕДНЯЯ(нить, оставил_символы + 1, ЛЕН(нить) – (оставил_символы + Правильно_символы)
Где:
- chars_left – количество символов для удаления слева.
- chars_right – количество символов для удаления справа.
Предположим, вы хотите извлечь имя пользователя из строки вида почта: София@gmail.com. Для этого часть текста нужно убрать с начала (Эл. адрес: – 7 символов) и с конца (@gmail.com – 11 символов).
Подайте вышеуказанные числа к формуле:
=СРЕДН(A2, 7+1, ДЛСТР(A2) – (7+10))
…и результат не заставит себя ждать:
Чтобы понять, что здесь происходит на самом деле, давайте вспомним синтаксис функции MID, которая используется для извлечения подстроки определенного размера из середины исходной строки:
MID(текст, start_num, num_chars)
текст Аргумент не вызывает вопросов – это исходная строка (в нашем случае A2).
Чтобы получить позицию первого символа для извлечения (start_num), вы добавляете 1 к количеству символов, которые нужно удалить слева (7+1).
Чтобы определить, сколько символов нужно вернуть (num_chars), вы вычисляете общее количество удаленных символов (7 + 11) и вычитаете сумму из длины всей строки: LEN(A2) – (7+10)).
Получить результат в виде числа
Какую бы из приведенных выше формул вы ни использовали, на выходе всегда будет текст, даже если возвращаемое значение содержит только числа. Чтобы вернуть результат в виде числа, либо заключите основную формулу в функцию ЗНАЧ, либо выполните какую-либо математическую операцию, которая не повлияет на результат, например, умножьте на 1 или добавьте 0. Этот метод особенно полезен, когда вы хотите вычислить результаты дальше. .
Предположим, вы удалили первый символ из ячеек A2:A6 и хотите просуммировать полученные значения. Удивительно, но тривиальная формула SUM возвращает ноль. Почему это? Очевидно, потому что вы складываете строки, а не числа. Выполните одну из следующих операций, и проблема будет устранена!
=ЗНАЧ(ЗАМЕНИТЬ(A2, 1, 1, “”))
=ВПРАВО(A2, ДЛИН(A2) – 1) * 1
= Удалить первые символы (A2, 1) + 0
Удалить первый или последний символ с помощью Flash Fill
В Excel 2013 и более поздних версиях есть еще один простой способ удалить первый и последний символы в Excel — функция мгновенного заполнения.
- В ячейке, смежной с первой ячейкой с исходными данными, введите желаемый результат, опуская первый или последний символ исходной строки, и нажмите Enter.
- Начните вводить ожидаемое значение в следующей ячейке. Если Excel обнаружит шаблон в данных, которые вы вводите, он будет следовать тому же шаблону в остальных ячейках и отображать предварительный просмотр ваших данных без первого/последнего символа.
- Просто нажмите клавишу Enter, чтобы принять предварительный просмотр.
Удаляйте символы по положению с помощью Ultimate Suite
Традиционно пользователи нашего Ultimate Suite могут справиться с задачей в несколько кликов, не запоминая кучу различных формул.
Чтобы удалить первые или последние n символов из строки, вам нужно сделать следующее:
- На Данные об аблебитах вкладка, в Текст щелкните Удалить > Удалить по положению.
- На панели надстройки выберите целевой диапазон, укажите, сколько символов нужно удалить, и нажмите Удалять.
Например, чтобы удалить первый символ, мы настраиваем следующую опцию:
Вот как удалить подстроку слева или справа в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Доступные загрузки
Удалить первый или последний символы — примеры (файл .xlsm)
Ultimate Suite – пробная версия (файл .exe)
