Извлечь первые N символов ячейки Excel можно формулой =ЛЕВСИМВ(A2;5) — она вернёт первые 5 символов текста из A2; первую букву даст =ЛЕВСИМВ(A2) (без второго аргумента — один символ). Чтобы оставить в самой ячейке только первые символы, используйте разбивку текста по столбцам с фиксированной шириной или замените формулы значениями. В Microsoft 365 первые цифры или буквы по шаблону извлекает =РЕГИЗВЛЕЧЬ(A2;"^\d+"), а обработать столбец на месте в 1 клик можно процедурой !SEMTools.
Простейшая задача - взять и отделить от ячейки первый символ, скопировав его в соседний столбец. Или выбрать и оставить в ней первые 2, 3, 4... N символов.
Например, в строках переменной длины первые 6 символов - определенный цифровой код, который нужно скопировать в отдельный столбец.
Или задача является частью другой, где нужно сделать первую букву ячейки заглавной, а для этого, ее нужно вытащить из ячейки.
Рассмотрим все известные решения, снабдив их примерами. Итак, поехали.
Первые N символов ячейки — формула ЛЕВСИМВ
Вытащить первый символ или N символов ячейки формулой проще простого. И есть даже 2 функции Excel, которые в этом помогут:
А так будут выглядеть формулы, извлекающие первые N символов из ячейки A1. Подставьте свое число вместо N, чтобы формулы работали:
=ЛЕВСИМВ(A1;N)
=ПСТР(A1;1;N)
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Первые 6 символов | Первая буква |
| 2 | 123456 Москва | 123456=ЛЕВСИМВ(A2;6) | 1=ЛЕВСИМВ(A2) |
| 3 | AB-7781 склад | AB-778=ЛЕВСИМВ(A3;6) | A=ЛЕВСИМВ(A3) |
| 4 | Петров Иван | Петров=ЛЕВСИМВ(A4;6) | П=ЛЕВСИМВ(A4) |
| 5 | Сочи | Сочи=ЛЕВСИМВ(A5;6) | С=ЛЕВСИМВ(A5) |
Если текст короче N символов (A5 «Сочи»), ЛЕВСИМВ вернёт его целиком, без ошибки. Результат всегда текстовый: «123456» в B2 — текст, а не число.
В английском Excel: =LEFT(A2,6)
Заметное отличие первой функции от второй — в отсутствии параметра, отвечающего за позицию, с которой начинается извлечение. Поэтому, если вычислить позицию первого вхождения определенного символа в ячейке, с помощью ПСТР можно извлечь его, даже если он не является первым символом строки.
Например:
- извлечь текст после цифр
- извлечь первые N цифр
- сделать первую букву ячейки заглавной
Первые символы как число
ЛЕВСИМВ всегда возвращает текст, поэтому извлечённые цифры не суммируются и не сравниваются с числами. Превратите результат в число двумя минусами: =--ЛЕВСИМВ(A2;6) или функцией ЗНАЧЕН.
Все символы, кроме последних N
Если известно, сколько символов нужно отбросить с конца, а не сколько оставить с начала, вычислите длину: =ЛЕВСИМВ(A2;ДЛСТР(A2)-3) оставит всё, кроме трёх последних символов. Подробнее — в статье как удалить последние символы.
Оставить в ячейке первые N символов: разбивка по столбцам
Можно получить первые N символов столбца, разбив его по количеству символов. Первые N останутся, а остальные будут перенесены в соседний справа. Можно разбить и на несколько — Excel не ставит ограничений на количество столбцов.
В отличие от формул, у процедур есть свои отличия, которые можно трактовать как плюсы и минусы в зависимости от ситуации. В данном случае отличие от формул в следующих моментах:
- разбивка по столбцам не сохраняет исходный столбец
- это единоразовая процедура, в отличие от формул, которые пересчитываются при обновлении данных
- удобный момент — одна процедура создает сразу несколько столбцов
- направление разбивки — только вправо
- если справа есть данные, они могут быть перезаписаны, если не отменить операцию и не создать пустой столбец/столбцы.
Извлечь первые символы мгновенным заполнением
Автозаполнение - замечательная процедура в Excel. Алгоритм действий прост.
- Заполняем пару ячеек соседнего столбца вручную;
- Уже при вводе третьей может быть предложено автозаполнение;
- Если согласны - жмём клавишу Ввод (Enter). Запустить заполнение принудительно можно сочетанием Ctrl+E или кнопкой «Данные → Мгновенное заполнение».
Однако с этой процедурой нужно быть осторожным. Алгоритм, который используется при автозаполнении, не показывается, поэтому результат может не соответствовать ожиданиям. Обычно алгоритм можно «доучить», чтобы он понял, что от него хотят.
Извлечь первые символы с помощью регулярных выражений
Чтобы выбрать из строки символы по регулярным выражениям, в Google Spreadsheets доступна функция REGEXEXTRACT. Там она существует уже очень давно.
В Excel для Microsoft 365 с 2024 года тоже есть функции регулярных выражений: РЕГИЗВЛЕЧЬ (REGEXEXTRACT), РЕГТЕСТ (REGEXTEST) и функция замены по шаблону REGEXREPLACE. Первые цифры строки извлекает =РЕГИЗВЛЕЧЬ(A2;"^\d+"), первые 5 символов — =РЕГИЗВЛЕЧЬ(A2;"^.{1,5}"), первое слово — =РЕГИЗВЛЕЧЬ(A2;"^\S+"). В Excel 2021 и раньше этих функций нет.
Для остальных версий Excel я включил поддержку регулярных выражений в формулы и процедуры своей надстройки !SEMTools.
Подробно тут - регулярные выражения в Excel и примеры регулярных выражений. А ниже на картинке - примеры формул, которые позволят извлечь первые N символов, в том числе букв или цифр по отдельности, из любой ячейки.
Функции доступны абсолютно бесплатно и работают при подключенной надстройке !SEMTools для Excel даже в стартовой версии.
Оставить первые / последние N символов в строке в 1 клик
Ну и напоследок - на панели !SEMTools доступны процедуры, позволяющие максимально легко и быстро выбрать первые или последние символы из ячеек.
Можно выделять одну ячейку, несколько, целый столбец или даже несколько несвязанных диапазонов - процедура обработает все выделенные данные. При желании можно вернуться назад на 1 шаг.
Если активна опция «Выводить результат справа», исходные данные не меняются, а извлеченные символы выводятся в соседний столбец/столбцы.
Смотрите демонстрацию ниже:
Процедуры доступны в полной версии надстройки, но попробовать можно и бесплатно.
Почему ЛЕВСИМВ возвращает не то
- Извлечённые цифры не суммируются. ЛЕВСИМВ возвращает текст. Добавьте два минуса: =--ЛЕВСИМВ(A2;6).
- Из даты извлекаются странные цифры. Дата хранится как число (например, 46000), и ЛЕВСИМВ берёт символы этого числа. Используйте =ЛЕВСИМВ(ТЕКСТ(A2;"ДД.ММ.ГГГГ");5) или функции ДЕНЬ, МЕСЯЦ, ГОД.
- Из числа с форматом извлекается не то, что видно. Формат «0,00» или «000123» не меняет значение: ЛЕВСИМВ работает с исходным числом. Оберните его в ТЕКСТ с нужным форматом.
- В начале результата пробел. В исходных ячейках лишние пробелы — сначала удалите их или используйте =ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);6).
- Ошибка #ЗНАЧ!. Количество символов отрицательное — например, в формуле ЛЕВСИМВ(A2;ДЛСТР(A2)-3) для текста короче трёх символов.
Первые символы в Excel — частые вопросы
Как извлечь первые 5 символов из ячейки?
Формулой =ЛЕВСИМВ(A2;5). То же даёт =ПСТР(A2;1;5).
Как оставить в ячейке только первые символы?
Извлеките их формулой в соседний столбец и замените формулы значениями, либо разбейте столбец через «Данные → Текст по столбцам» с фиксированной шириной.
Как оставить только первую букву?
Формулой =ЛЕВСИМВ(A2) — без второго аргумента ЛЕВСИМВ возвращает один символ.
Какая функция VBA извлекает первые символы строки?
Функция Left: Left("Москва", 3) вернёт «Мос». Для символов из середины строки — Mid, с конца — Right.
Оставить первые или последние N символов, первые цифры или буквы сразу во всём выделенном диапазоне — на месте или с выводом в соседний столбец — можно в 1 клик с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь первые N символов в Excel
| Задача | Способ |
|---|---|
| Первые N символов | =ЛЕВСИМВ(A2;N) |
| Первая буква | =ЛЕВСИМВ(A2) |
| Первые цифры по шаблону | =РЕГИЗВЛЕЧЬ(A2;"^\d+") (Microsoft 365) |
| Оставить первые символы в самой ячейке | Текст по столбцам, фиксированная ширина |
| Весь диапазон на месте | Процедура !SEMTools в 1 клик |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: удалить последние символы и сделать первую букву заглавной.
имеется формула генерации случайных символов (пароля), которую нужно растянуть на весь столбец в таблице. Если просто растянуть, то при изменении данных в любой ячейке таблицы — во всех ячейках отработает формула генерации и все данные изменятся. Ищу способ, чтобы в одной ячейке задать формулу, а в столбце, где нужно растянуть на весь лист столбец, зафиксировать разово полученный результат.
Я бы посоветовал протянуть формулу, а потом удалить формулы и оставить значения
Искренне благодарю, наконец разобралась с принципом, как вытянуть часть текста.