Удалить последний символ / N символов в конце ячеек Excel

Удалить последние символы в ячейках Excel – одна из ваших повседневных задач, если вы много работаете с текстовыми данными в этом замечательном табличном редакторе.

Вырезать символы в конце ячеек

Если под понятием “Вырезать символы” вы подразумеваете удалить окончательно, то читайте эту статью дальше.

Если же речь о том, чтобы вырезать и вставить в соседнюю ячейку – смотрите статью “Как извлечь символы по их позиции в ячейке“.

Когда символы в конце ячеек могут стать лишними

Лишние символы в конце ячеек могут возникнуть по самым различным причинам:

• Лишние пробелы в ячейках — часто появляются при копировании текста с сайтов, PDF или других файлов. Такие пробелы мешают сортировке, поиску и работе формул (“ABC” ≠ “ABC “).
• Лишние запятые или точки — например, когда список значений формировался вручную и последняя строка заканчивается на “,” или “.”.
• Символы-разделители — точка с запятой, табуляция или спецсимвол, добавленные при экспорте данных из других программ.
• Ненужные переносы строк (СИМВОЛ(10) или СИМВОЛ(13)) — часто встречаются при импорте данных из CSV, XML или копировании из интернета.
• Дубликаты символов — например, слово оканчивается на двойной знак (точка, скобка, кавычка), и нужно оставить только один.
• Технические символы — например, невидимые управляющие знаки Unicode (пробел нулевой ширины, неразрывный пробел), которые остаются после загрузки данных.
• Маркировка или теги — иногда данные экспортируются вместе с суффиксами вроде “_old”, “_copy”, которые нужно убрать для чистки базы.
• Форматирование чисел — встречаются ситуации, когда число загружается как текст с лишним знаком (например, “1234,”), что мешает конвертации в число.
• Определённые параметры в URL (как правило идут после “?”, “&” или “#”)

Убрать символы в конце ячейки может показаться простой задачей, но на самом деле существуют множество нюансов, которые могут превратить её в тот ещё челлендж.

Однако, какой бы ни была сложность в вашем конкретном случае, эта статья поможет вам распутать клубок. Все примеры ниже будут сопровождены скриншотами, и короткими видеопримерами для пользователей моей надстройки (как то же самое делать с ней).

Удалить последний символ

Чтобы убрать последний символ в ячейке, используем функции ЛЕВСИМВ и ДЛСТР:

=ЛЕВСИМВ(A1;ДЛСТР(A1)-1)

Эта формула берет текст из ячейки A1 и обрезает его на один символ с конца.

А так это можно делать моментально во всём столбце или диапазоне, и прямо на месте:

Удалить последние N символов

Если нужно удалить сразу несколько последних символов, например 2, в формуле выше нужно заменить 1 на число символов, которые мы хотим убрать:

=ЛЕВСИМВ(A1;ДЛСТР(A1)-2)

А так это сделать в Excel с !SEMTools:

Удалить определённый символ в конце ячейки (только если он там есть)

Например, если в конце ячейки стоит точка или другой определенный символ, и его нужно удалить только при наличии:

=ЕСЛИ(ПРАВСИМВ(A1;1)=".";ЛЕВСИМВ(A1;ДЛСТР(A1)-1);A1)

Так будет выглядеть на реальных примерах

Удалить несколько окончаний по условию

Бывает задача привести слова к единой форме: например, убрать окончания ные, ных, ным, ная и т.д., чтобы заменить их на одно окончание “ный”.

=ЕСЛИ(СУММ(--(ПРАВСИМВ(A1;3)={"ные";"ных";"ным"}))>0;ЛЕВСИМВ(A1;ДЛСТР(A1)-3)&"ный";A1)

Здесь проверяется окончание слова, и при совпадении оно заменяется на общее — ный.

Удаление окончаний в компьютерной лингвистике зовётся словом стемминг, а приведение слов к начальной форме – лемматизация. Кстати, на эту тему в надстройке !SEMTools тоже есть инструменты. Смотрите статью: Лемматизация текста в Excel.

Удалить последнее вхождение символа

Редкая и непростая задача — удалить именно последнее вхождение символа (например, последнюю запятую). Решение строится на подсчете количества вхождений и использовании функции ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(A1;",";" и ";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";"")))

Формула выше вычисляет общее количество символов-запятых, удаляет последнюю запятую и заменяет её на ” и “. Так выглядит результат применения формулы:

В данном случае речь не о буквально последнем символе в ячейке. А о последнем определённом символе.

Удаление последних символов с помощью REGEXREPLACE

Очень гибкий способ удаления символов — это использование регулярных выражений (в простонародье регулярок). Среди популярнейших табличных редакторов функция REGEXREPLACE впервые появилась в Google Таблицах, затем была реализована в надстройке !SEMTools, а теперь доступна и в последних обновлениях Excel 365.

Функции регулярных выражений в !SEMTools доступны в бесплатной версии бессрочно. Поэтому, если ваш Excel не поддерживает регулярки, надстройка может быть вам крайне полезна :)

Ключевым символом всех шаблонов при удалении символов в конце ячеек является “$” – символ конца строки (в нашем случае он и означает конец содержимого ячейки).

Регулярные выражения часто позволяют избегать громоздких формул, которые воспроизвести по памяти смогут лишь настоящие гуру Excel. При этом их базовый синтаксис не так уж и сложен. Это видно на примерах ниже.

Удалить последний символ

Выражение “.$” означает «любой символ в конце строки»:

=REGEXREPLACE(A1;"."$";"")

Удалить последние N символов

Чтобы убрать несколько символов в конце (например, 3), используем шаблон:

.{3}$

=REGEXREPLACE(A1;"."{3}$";"")

Удалить все цифры в конце ячейки

Если цифры в конце ячейки – единственные, и других цифр в ячейке больше нет, есть варианты и проще, смотрите статью “Удалить цифры в ячейках Excel“. Здесь же речь о ситуациях, когда в начале и середине текста ячеек могут быть другие цифры, которые удалять не нужно.

Шаблон: \d+$

=REGEXREPLACE(A1;"\d+$";"")

Для быстрого удаления по регулярному выражению без формул есть процедура в меню “Удалить символы” на панели !SEMTools для Excel:

Есть и другой вариант – удалить последние два числа в конце ячейки и любые символы между ними. Шаблон будет таким:

\d+[^\d]+\d+$

Так это выглядит на таблице из примера выше:

Вас, наверняка, заинтересовал вопрос: а можно ли вырезать и вставить эти размеры рядом? Да, это легко сделать с помощью процедуры “Извлечь символы по регулярному выражению“. Так одним шаблоном мы можем разделить столбец на два столбца – вырезанное значение, и то, что осталось после удаления:

Удалить все буквы (алфавитные символы) в конце ячейки

Шаблон (латиница+кириллица):

[A-Za-zА-Яа-яЁё]+$

=REGEXREPLACE(A1;"[A-Za-zА-Яа-яЁё]+$";"")

Смотрите также статью: удалить латиницу из текста в Excel.

Удалить n букв в конце ячейки

Шаблон:

[A-Za-zА-Яа-яЁё]{n}$

Пример для n=3:

=REGEXREPLACE(A1;"[A-Za-zА-Яа-яЁё]{3}$";"")

Удалить n цифр в конце ячейки

Шаблон:

\d{n}$
Пример для n=4:

=REGEXREPLACE(A1;"\d{4}$";"")

Удалить любые знаки после определённого символа в конце ячейки

Допустим, в конце строки может идти символ #, после которого иногда «прилипают» любые лишние знаки — их нужно убрать, а сам # оставить.

Шаблон:

(#).+$

Преимущества регулярных выражений

• Лаконичные формулы без вложенных функций;
• Гибкость — можно сразу обработать несколько условий;
• Совместимость — доступно в Google Sheets, !SEMTools и Excel 365.

Подробнее о синтаксисе и шаблонах “регулярок” смотрите в соответствующей статье.

Удалить слова и символы по другим условиям

Надеюсь, на этой странице вы нашли то, что искали, и смогли решить задачу удаления последних или первых символов в ячейках.

Вас наверняка могут заинтересовать похожие операции, смотрите подробнее:

• Удалить первые N символов в ячейках Excel
• Взять первые N символов ячейки в Excel;
• Удалить лишние пробелы в Excel;
• Удалить латиницу;
• Удалить все символы, кроме цифр в Excel;
• Удалить все символы, кроме букв и цифр;
• Удалить первое слово или первые N слов в ячейках;
• Регулярные выражения в Excel.