Работа с текстовыми данными в Excel: поиск и извлечение

Иван Корнев·21.05.2024·⏱4 мин

Чтобы извлечь часть строки или найти позицию символа в Excel, используйте комбинацию функций LEFT, RIGHT, MID вместе с FIND или SEARCH. В современных версиях (Excel 365, 2021) задачу упрощают функции TEXTBEFORE, TEXTAFTER и TEXTSPLIT. Эти инструменты позволяют автоматически разделять имена и фамилии, выделять домены из email или очищать данные от лишних символов без ручного копирования.

Базовые функции для работы с текстом

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

LEFT(текст; число_символов) — возвращает указанное количество символов с начала строки.
RIGHT(текст; число_символов) — возвращает символы с конца строки.
MID(текст; начальная_позиция; число_символов) — вырезает фрагмент из середины, начиная с заданной позиции.

Нумерация символов в Excel начинается с единицы. Первый символ строки имеет индекс 1, а не 0.

Однако статические функции знают только количество символов. Чтобы динамически находить нужные данные (например, всё, что стоит до знака «@»), необходимо определить позицию этого знака. Для этого используются:

FIND(что_искать; где_искать) — находит позицию символа с учетом регистра (А ≠ а).
SEARCH(что_искать; где_искать) — находит позицию без учета регистра и поддерживает маски (* и ?).

Извлечение данных с помощью разделителей

Самая частая задача — разбить строку по конкретному знаку (разделителю): запятой, пробелу, дефису или слешу.

Классический метод (универсальный)

Если нужно получить текст до определенного символа, комбинируют LEFT и FIND. Формула вычисляет позицию разделителя и отнимает 1, чтобы не захватить сам знак.

Пример: Извлечь имя из строки «Иванов Иван» (разделитель — пробел): =LEFT(A2; FIND(" "; A2) - 1)

Пример: Извлечь домен из email «[email protected]»: =MID(A2; FIND("@"; A2) + 1; LEN(A2)) Здесь +1 нужно, чтобы начать чтение сразу после собаки, а LEN гарантирует захват всего остатка строки.

Современный метод (Excel 365 и 2021+)

В новых версиях появились интуитивно понятные функции, которые заменяют сложные конструкции:

=TEXTBEFORE(A2; "@") — вернет всё, что стоит до символа @.
=TEXTAFTER(A2; "@") — вернет всё, что стоит после символа @.
=TEXTSPLIT(A2; ",") — разобьет строку на массив данных по запятой, автоматически заполнив соседние ячейки.

Функции TEXTBEFORE и TEXTAFTER имеют аргумент номер_вхождения. Это позволяет легко извлекать текст после второго или третьего разделителя, что ранее требовало вложенных формул.

Поиск значения и обработка ошибок

При поиске символа, которого нет в ячейке, функции FIND и SEARCH возвращают ошибку #ЗНАЧ!. Это ломает дальнейшие расчеты.

Проверка наличия подстроки

Чтобы просто узнать, содержится ли слово в тексте, используйте связку с ISNUMBER (ЕСТЬЧИСЛО): =ISNUMBER(FIND("код"; A2)) Формула вернет ИСТИНА, если слово найдено, и ЛОЖЬ, если нет.

Безопасное извлечение

Чтобы формула не выдавала ошибку при отсутствии разделителя, оберните её в IFERROR (ЕСЛИОШИБКА): =IFERROR(TEXTAFTER(A2; "-"); "Разделитель не найден") Или верните исходное значение, если изменения не нужны: =IFERROR(TEXTAFTER(A2; "-"); A2)

Практические примеры решения задач

Задача	Формула (классическая)	Формула (Excel 365)
Получить расширение файла (из "doc.txt")	`=RIGHT(A2; LEN(A2)-FIND("."; A2))`	`=TEXTAFTER(A2; ".")`
Извлечь код города (из "+7 (999)...")	`=MID(A2; FIND("(")+1; FIND(")")-FIND("(")-1)`	`=TEXTBEFORE(TEXTAFTER(A2;"("); ")")`
Убрать первые 3 символа	`=RIGHT(A2; LEN(A2)-3)`	`=TEXTAFTER(A2; LEFT(A2;3))`*

*Примечание: для простого удаления фиксированного числа символов лучше использовать классический RIGHT или MID.

Частые ошибки и как их избежать

Ошибка #ЗНАЧ! при поиске. Возникает, если искомый символ отсутствует. Всегда используйте IFERROR или предварительно проверяйте данные функцией ISNUMBER.
Лишний пробел в результате. При использовании FIND(" "; A2) - 1 легко ошибиться в арифметике, захватив пробел. Внимательно проверяйте знаки +1 и -1. Функция TRIM (СЖПРОБЕЛЫ) поможет убрать лишние пробелы вокруг результата.
Проблема с регистром. Функция FIND различает "Apple" и "apple". Если регистр не важен, всегда используйте SEARCH или приводите текст к одному виду через LOWER (СТРОЧН) / UPPER (ПРОПИСН).
Неверная позиция последнего разделителя. Стандартный FIND находит первое вхождение. Чтобы найти последнее (например, точку в пути к файлу C:\Folder\File.txt), требуется сложная формула с заменой символов или использование аргумента номер_вхождения_с_конца в новых функциях TEXTBEFORE(...; ; -1).

FAQ

Как извлечь текст между двумя скобками? Используйте вложение функций: =MID(A2; FIND("("; A2)+1; FIND(")"; A2)-FIND("("; A2)-1). В Excel 365 проще: =TEXTBEFORE(TEXTAFTER(A2; "("); ")").

Можно ли искать несколько символов сразу? Да, в функции SEARCH можно использовать маску * (любое количество символов). Например, SEARCH("товар*2024"; A2) найдет фразу, где между словами есть любой текст.

Что делать, если в ячейке несколько одинаковых разделителей? Классические функции найдут только первый. Используйте параметр номер_вхождения в FIND/SEARCH или функцию TEXTSPLIT, которая разобьет строку на части, и затем обращайтесь к нужному элементу массива.