Извлечь последние N символов ячейки Excel можно формулой =ПРАВСИМВ(A2;4) — она вернёт 4 последних символа, например последние 4 цифры номера карты или телефона. Последний символ — =ПРАВСИМВ(A2), последнее слово — =ТЕКСТПОСЛЕ(A2;" ";-1;;;A2) в Excel 365, цифры в конце строки — регулярным выражением. Оставить последние символы прямо в ячейках без формул можно процедурой !SEMTools в 2 клика.
При работе с текстом часто бывает нужно отделить нескольких ячеек столбца последние символы, скопировав их в соседний. Или оставить прямо в самих ячейках столбца последние 2, 3, 4... N символов.
К сожалению, встроенной процедуры, которая позволила бы это сделать парой кликов мыши, в Excel нет, но зато можно использовать формулу.
Последние N символов ячейки — формула ПРАВСИМВ
Вывести в отдельную ячейку последний символ или N символов ячейки формулой довольно просто. В этом поможет функция ПРАВСИМВ.
Пример с извлечением одного символа из ячейки A1:
=ПРАВСИМВ(A1;1)
Для N символов укажите число вторым аргументом: =ПРАВСИМВ(A1;4). Если текст короче N, функция вернёт его целиком без ошибки.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Последние 4 | Последнее слово |
| 2 | 4276 1234 5678 9012 | 9012=ПРАВСИМВ(A2;4) | 9012=ЕСЛИОШИБКА(ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК("\";ПОДСТАВИТЬ(A2;" ";"\";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;" ";"")))));A2) |
| 3 | Артикул 778812 | 8812=ПРАВСИМВ(A3;4) | 778812=ЕСЛИОШИБКА(ПРАВСИМВ(A3;ДЛСТР(A3)-ПОИСК("\";ПОДСТАВИТЬ(A3;" ";"\";ДЛСТР(A3)-ДЛСТР(ПОДСТАВИТЬ(A3;" ";"")))));A3) |
| 4 | 1234567 | 4567=ПРАВСИМВ(A4;4) | 1234567=ЕСЛИОШИБКА(ПРАВСИМВ(A4;ДЛСТР(A4)-ПОИСК("\";ПОДСТАВИТЬ(A4;" ";"\";ДЛСТР(A4)-ДЛСТР(ПОДСТАВИТЬ(A4;" ";"")))));A4) |
ПРАВСИМВ всегда возвращает текст: «4567» из числа 1234567 — уже не число. Чтобы получить число, добавьте два минуса: =--ПРАВСИМВ(A4;4). Если в ячейке нет пробелов (A4), формула последнего слова возвращает ячейку целиком.
В английском Excel: =RIGHT(A2,4)
Извлечь последние символы до пробела или по шаблону
Часто нужно взять не фиксированное число символов, а всё, что стоит после последнего пробела: последнее слово, единицу измерения, размер. Тогда количество символов придётся вычислить:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК("\";ПОДСТАВИТЬ(A1;" ";"\";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))));A1)
Формула заменяет последний пробел на редкий символ, находит его позицию и забирает всё, что правее. Если пробелов в ячейке нет, она вернёт ячейку целиком. Подробно механика разобрана в статье про поиск с конца строки.
В Excel 365 то же самое делает одна функция (последний аргумент — что вернуть, если пробела нет; без него будет ошибка #Н/Д):
=ТЕКСТПОСЛЕ(A1;" ";-1;;;A1)
А если нужны, например, только цифры в конце ячейки, проще всего воспользоваться регулярным выражением. Функция REGEXEXTRACT из !SEMTools работает в любой версии Excel, даже в бесплатной версии надстройки:
=REGEXEXTRACT(A1;"\d+$")
Последние 2 цифры числа в конце строки извлекает =REGEXEXTRACT(A1;"\d{2}$"), а в Excel 365 — встроенная =РЕГИЗВЛЕЧЬ(A1;"\d+$").
Оставить последние N символов в ячейках в 2 клика
Поскольку формулы - это долго, всегда требуют создания дополнительных столбцов, я разработал в своей надстройке соответствующие процедуры.
Они доступны с панели !SEMTools в пару кликов. Извлекаемое количество придется вбить в предназначенное для этого окошко ввода.
Преимущества решения:
- Процедура обработает все выделенные данные. Можно выделять
- одну ячейку,
- несколько,
- целый столбец
- или даже несколько несвязанных диапазонов
- При желании можно вернуться назад на 1 шаг
- Есть опция "Выводить результат справа". Позволяет вывести извлеченные данные в соседний столбец или столбцы.
Пример ниже:
Процедуры доступны в полной версии надстройки, но попробовать можно и бесплатно.
P.S. вероятно, после отделения последних символов в соседний столбец, вам может потребоваться удалить последние N символов в ячейках исходного столбца.
Почему ПРАВСИМВ возвращает не то
- Извлечённые цифры не считаются числом. ПРАВСИМВ возвращает текст. Используйте =--ПРАВСИМВ(A1;4).
- Последние символы «пустые». В конце ячейки пробелы — удалите их или используйте ПРАВСИМВ(СЖПРОБЕЛЫ(A1);4).
- У длинного номера пропали последние цифры. Число длиннее 15 цифр (номер карты, счёта) Excel хранит с нулями в конце. Такие номера нужно хранить как текст.
- Из даты извлекаются странные цифры. Дата — это число (например, 46000). Используйте ПРАВСИМВ(ТЕКСТ(A1;"ДД.ММ.ГГГГ");4) или функцию ГОД.
Последние символы в Excel — частые вопросы
Как оставить только последние 4 цифры?
Формулой =ПРАВСИМВ(A1;4) в соседнем столбце, затем замените формулы значениями. Или процедурой !SEMTools прямо на месте.
Как извлечь последний символ ячейки?
Формулой =ПРАВСИМВ(A1) — без второго аргумента функция возвращает один символ.
Как отделить последнее слово в ячейке?
В Excel 365 — =ТЕКСТПОСЛЕ(A1;" ";-1;;;A1), в других версиях — формулой с ПОДСТАВИТЬ из статьи.
Как извлечь последние цифры из текста?
Регулярным выражением =REGEXEXTRACT(A1;"\d+$") — все цифры в конце строки.
Оставить последние N символов, последние цифры или последнее слово сразу во всех выделенных ячейках — на месте или с выводом в соседний столбец — можно в 2 клика с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь последние символы в Excel
| Задача | Способ |
|---|---|
| Последние N символов | =ПРАВСИМВ(A1;N) |
| Последний символ | =ПРАВСИМВ(A1) |
| Последнее слово (Excel 365) | =ТЕКСТПОСЛЕ(A1;" ";-1;;;A1) |
| Цифры в конце строки | =REGEXEXTRACT(A1;"\d+$") |
| Оставить символы на месте | Процедура !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: извлечь первые N символов и удалить последние символы.