Текстовые функции в Excel: как разобрать строку, найти символ и перевести число

Выгрузка из учётной системы приходит одной строкой: артикул вместе со складом, ФИО целиком, сумма вперемешку с буквами. Справочник Microsoft относит к этой группе ЛЕВСИМВ, ПРАВСИМВ и ПСТР: первые две берут крайние знаки строки, третья — знаки с заданной позиции^[2]. Ручная правка таких ячеек занимает часы и повторяется при каждой новой выгрузке.

Текстовые функции Excel решают задачу одной формулой на весь столбец. Порядок работы одинаков: найти подходящую функцию в справочнике, ввести формулу с аргументами через точку с запятой, проверить результат на строке без разделителя, исправить ошибку #ЗНАЧ! и протянуть формулу на свои данные. Главный риск — тихий промах: результат ПСТР выглядит как число, но остаётся строкой, и сумма по столбцу не сходится.

Ключевые факты: текстовые функции Excel

• Текстовые функции работают со строкой как с цепочкой знаков: считают их, вырезают часть и ищут позицию нужного символа.
• Ячейка вмещает не больше 32 767 знаков, а запись формулы — не больше 8 192 знаков^[3].
• ПСТР возвращает #ЗНАЧ!, если начальная позиция меньше 1 или число знаков отрицательное^[1].
• НАЙТИ отличается от ПОИСК учётом регистра: первая различает «А» и «а», вторая — нет^[2].
• Справка Microsoft советует хранить исходное число в одной ячейке, а функцию ТЕКСТ применять в другой^[4].

Какие текстовые функции Excel нужны для разбора строк

Текстовые функции Excel считают знаки, вырезают часть строки и ищут позицию символа. Каждая принимает строку в кавычках или ссылку на ячейку, а аргументы в русской версии разделяются точкой с запятой. Все примеры ниже считаются на одном листе:

Таблица собирает функции, которые закрывают большую часть задач со строками. Результаты пересчитаны в движке тренажёра на данных листа выше^[7].

Текстовые функции Excel почти всегда работают в связке из двух-трёх формул: одна находит позицию разделителя, вторая вырезает кусок, третья меняет тип результата. Регистр меняют ПРОПИСН, СТРОЧН и ПРОПНАЧ — справка Microsoft приводит их среди способов очистки данных. Текст внутри формулы пишется только в кавычках: без них Excel примет слово за имя и вернёт #ИМЯ?.

Разобравшись с набором функций, переходим к главной задаче — извлечь часть строки, когда длина куска заранее известна или меняется от ячейки к ячейке.

Как извлечь часть строки: ЛЕВСИМВ, ПРАВСИМВ и ПСТР

Функция ПСТР возвращает заданное число знаков из середины строки с указанной позиции. Синтаксис по справке Microsoft — ПСТР(текст;начальная_позиция;число_знаков), а ЛЕВСИМВ и ПРАВСИМВ обходятся двумя аргументами: строкой и количеством знаков с края^[1]. Если второй аргумент ЛЕВСИМВ опущен, функция возвращает 1 знак, а количество больше длины строки возвращает весь текст^[5].

Текстовые функции Excel этой тройки отличаются только точкой отсчёта: начало строки, конец или произвольная позиция. Когда длина куска постоянна, хватает одной функции: код склада в «АРТ-2045-МСК» — всегда три последних знака, =ПРАВСИМВ(A3;3). Когда длина меняется, как у имён, позицию разделителя находит ПОИСК. Разделим «Иван Петров» на имя и фамилию:

1. Выберите B2 и введите =ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1) — функция вернёт «Иван».
2. Выберите C2 и введите =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2)) — функция вернёт «Петров».
3. Протяните обе формулы вниз на весь список и проверьте строку без пробела.

Справка Microsoft о разделении текста по столбцам даёт для имени запись =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1))^[6]. Формула имени из справки Microsoft захватывает пробел: позиция пробела равна пяти, и ЛЕВСИМВ берёт пять знаков. Вычитание единицы из позиции пробела убирает его, и сравнение имён в другой таблице не ломается.

Строка «Ольга» без пробела ломает обе формулы: ПОИСК не находит разделитель и возвращает #ЗНАЧ!^[7]. Исправить ошибку помогает обёртка =ЕСЛИОШИБКА(ЛЕВСИМВ(A4;ПОИСК(" ";A4)-1);A4) — для такой строки формула вернёт всё имя целиком.

Однако позиция разделителя устроена иначе, чем кажется: одна и та же буква находится на разных местах в зависимости от функции поиска.

Чем ПОИСК отличается от НАЙТИ и зачем им ДЛСТР

Функция ПОИСК ищет позицию подстроки без учёта регистра, НАЙТИ — с учётом^[2]. Вторая функция различает заглавные и строчные буквы, первая их не замечает. Для слова «Анна» =ПОИСК("а";"Анна") возвращает 1, потому что заглавная «А» стоит первой, а =НАЙТИ("а";"Анна") возвращает 4 — строчная «а» стоит четвёртой^[7].

По справке Microsoft ПОИСК принимает подстановочные знаки: вопросительный знак заменяет один символ, звёздочка — любую последовательность, а тильда перед ними ищет сам знак^[8]. Начальная позиция по умолчанию равна 1, а позиция 0 или больше длины строки возвращает #ЗНАЧ!. Справка приводит пример =ПОИСК("н";"принтер") с результатом 4.

ДЛСТР дополняет поиск: функция считает знаки вместе с пробелами, и через неё вычисляется длина правой части строки. Для фамилии из примера выше длина 11 минус позиция пробела даёт число знаков справа.

Количество слов в ячейке считает разность ДЛСТР с пробелами и без пробелов плюс один: =ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;" ";""))+1. Разность длин даёт число пробелов, а единица добавляет последнее слово — для «Иван Петров» формула вернёт 2^[7]. Перед подсчётом строку стоит очистить СЖПРОБЕЛЫ, иначе двойной пробел даст лишнее слово.

Именно поэтому перевод результата в число требует отдельного разбора: найденный и вырезанный кусок остаётся строкой.

Почему ТЕКСТ и ЗНАЧЕН меняют тип значения

Функция ТЕКСТ превращает число в строку по коду формата, а ЗНАЧЕН выполняет обратное преобразование^[4]. Синтаксис по справке Microsoft — ТЕКСТ(значение;формат), где код формата стоит в кавычках, и ЗНАЧЕН(текст) с одним аргументом^[9].

Текстовые функции возвращают строку, даже если внутри одни цифры. =ПСТР(A3;5;4) вернёт «2045», и проверка =ЕТЕКСТ(ПСТР(A3;5;4)) покажет ИСТИНА. Арифметика со знаком плюс переводит такую строку сама, а =ЗНАЧЕН(ПСТР(A3;5;4))+1 даёт 2046 явно^[7]. Справка Microsoft добавляет, что ЗНАЧЕН обычно не нужна: Excel сам преобразует значения, а функция оставлена для совместимости с другими программами.

ТЕКСТ нужна там, где число или дату склеивают с подписью. При записи =A2&" "&B2 формат даты теряется и вместо даты видно число, поэтому справка советует =A2&" "&ТЕКСТ(B2;"дд.мм.гггг")^[4]. Разделитель разрядов в коде формата зависит от региональных параметров компьютера.

Таблица ниже связывает симптом, причину и действие для трёх частых сбоев.

Ведущие нули в кодах сохраняет тот же приём: =ТЕКСТ(1234;"0000000") вернёт 0001234. Подробнее о склейке строк с датой и числом — на странице о функциях СЦЕП и операторе &.

Остаётся понять, в каких задачах формулы со строками проигрывают встроенным инструментам и новым функциям.

Когда текстовые функции не подойдут

Текстовые функции не подойдут, если правило разбора нельзя описать позицией разделителя или длина строки выходит за пределы Excel. Ячейка вмещает 32 767 знаков, а запись формулы — до 8 192 знаков^[3], поэтому длинные вложенные конструкции лучше заменить другим способом.

• Если строку нужно разрезать на много частей по одному разделителю, вложенные ПОИСК становятся нечитаемыми — в Microsoft 365 для этого есть отдельная функция, разбор на странице о разделении текста по разделителю.
• Если в выгрузке стоит неразрывный пробел с кодом 160, СЖПРОБЕЛЫ его не удалит — сначала замените знак, как описано на странице о чистке неразрывных и двойных пробелов.
• При задаче только изменить вид числа в ячейке функция ТЕКСТ лишняя: формат ячейки меняет отображение и оставляет число числом.
• Когда файл откроют в Google Таблицах, ЛЕВСИМВ без второго аргумента там тоже вернёт 1 символ, а 0 допустим как значение^[11]; новые функции из Microsoft 365 проверяйте отдельно.

Версии в справочнике указаны общим списком: Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016, включая Mac^[2]. Текстовые функции Excel из этого списка, кроме новых функций с пометкой Office 365, работают и в Google Таблицах под теми же русскими именами. Остальные группы формул — счёт, поиск, условия — собраны в сводке формул Excel по группам задач. Повторите разбор ФИО в тренажёре: урок меняет строки на листе и проверяет, что формула работает без ручного подсчёта знаков.