Как извлекать части текста в Excel: LEFT, RIGHT и MID
Работа с текстовыми или смешанными данными в Excel часто требует извлечения части строки: имя пользователя из e-mail, номер из кода товара или последние четыре цифры телефона. Для таких задач служат три базовые функции: LEFT, RIGHT и MID. Они просты и быстры в использовании, но выбор правильной функции и учёт особенностей данных экономит время.
В этой статье вы найдёте:
- Пошаговые примеры использования LEFT, RIGHT и MID.
- Чек‑лист и шпаргалку с готовыми формулами.
- Альтернативы и рекомендации, когда применять другие инструменты.
- Краткий план тестирования и критерии приёмки.
Что делает каждая функция (в одно предложение)
- LEFT(text, num_chars) — возвращает num_chars символов, начиная с левой стороны текста.
- RIGHT(text, num_chars) — возвращает num_chars символов, начиная с правой стороны текста.
- MID(text, start, num_chars) — возвращает num_chars символов, начиная со позиции start (номер символа, где 1 = первый).
Важно: позиция символа и длина считаются по символам, а не по байтам. Если в ячейках есть скрытые пробелы, сначала примените TRIM или CLEAN.
Как использовать LEFT для извлечения текста слева
Синтаксис:
LEFT(text, num_chars)text — строка или ссылка на ячейку; num_chars — количество символов, которое нужно вернуть.
Пример 1 — извлечь имя пользователя из e-mail (до символа @):
=LEFT(A2, FIND("@", A2) - 1)Объяснение: FIND находит позицию «@», вычитаем 1, чтобы не включать символ «@», и LEFT возвращает все символы до него.
Пример 2 — первые 2 буквы кода товара (код вида AA123):
=LEFT(A2, 2)Подсказка: если строки имеют разную длину и вы не знаете, есть ли символы-разделители, рассмотрите TEXTBEFORE (Excel 365) или Power Query.
Как использовать RIGHT для извлечения текста справа
Синтаксис:
RIGHT(text, num_chars)Пример — отделить числовую часть кода товара (последние 3 цифры):
=RIGHT(A2, 3)Ещё пример — последние 4 цифры телефонного номера в формате (XXX) XXX-XXXX:
=RIGHT(A2, 4)Подсказка: если нужно удалить дефисы или пробелы, сочетайте RIGHT с SUBSTITUTE или используйте TEXTSPLIT в новых версиях.
Как использовать MID для извлечения из середины строки
Синтаксис:
=MID(text, start, num_chars)- text — строка или ссылка;
- start — позиция первого символа (1 = первый символ);
- num_chars — количество символов для извлечения.
Пример — средняя часть телефонного номера (трёхзначная группа в формате (XXX)XXX-XXXX):
=MID(A2, 6, 3)Пример — извлечь домен из e-mail после @:
=MID(A2, FIND("@", A2) + 1, LEN(A2))Здесь FIND находит «@», затем мы начинаем со следующего символа, а LEN(A2) гарантирует, что MID вернёт всю оставшуюся часть строки.
Шпаргалка: быстрые формулы
| Задача | Формула | Что возвращает |
|---|---|---|
| Первые N символов | =LEFT(A2, N) | N символов слева |
| Последние N символов | =RIGHT(A2, N) | N символов справа |
| N символов из середины | =MID(A2, start, N) | N символов, начиная с позиции start |
| Имя пользователя из e‑mail | =LEFT(A2, FIND(“@”, A2)-1) | Всё до «@» |
| Домен из e‑mail | =MID(A2, FIND(“@”,A2)+1, LEN(A2)) | Всё после «@» |
| Удалить пробелы | =TRIM(A2) | Убирает лишние пробелы |
Когда LEFT/RIGHT/MID не подходят (контрпример)
- Разделитель нефиксирован или разных типов (например, адреса с запятыми и точками): лучше Power Query или TEXTSPLIT.
- Неоднородные шаблоны внутри столбца (иногда код длиной 2, иногда 4): используйте регулярную обработку в Power Query или формулы с IF и LEN.
- Для больших объёмов данных и повторяемых преобразований — Power Query даёт воспроизводимость и масштабируемость.
Альтернативы и когда их использовать
- TEXTBEFORE / TEXTAFTER (Excel 365): проще при явных разделителях.
- TEXTSPLIT (Excel 365) / SPLIT (Google Sheets): когда нужно разбить строку на части по разделителю.
- Flash Fill (Заполнение по образцу): быстрый вариант для одноразовых преобразований.
- Power Query: для очистки, нормализации и массовых преобразований данных.
Мини‑правило: если задача одноразовая и шаблон прост — LEFT/RIGHT/MID. Если нужна повторяемость, логика или масштаб — Power Query.
Ментальные модели и эвристики
- Фиксированная позиция? — используйте LEFT/RIGHT/MID.
- Разделитель / символ-ограничитель? — TEXTBEFORE/TEXTAFTER или TEXTSPLIT.
- Несколько шаблонов / грязные данные? — Power Query.
- Нужно быстро и вручную на паре строк? — Flash Fill.
Примерный план действий (мини‑методология)
- Осмотрите данные: есть ли разделители? все ли строки похожи?
- Выберите инструмент: формула / TEXTSPLIT / Power Query.
- Напишите и протестируйте формулу на 5–10 строках.
- Проверьте пограничные случаи (пустые значения, лишние пробелы, отсутствующий разделитель).
- Автоматизируйте: примените формулу вниз по столбцу или настройте Power Query.
Чек-листы по ролям
Аналитик данных:
- Проверить наличие/тип разделителя.
- Протестировать формулы на примерах.
- Убедиться, что результат совпадает с эталоном.
BI‑разработчик:
- Использовать Power Query для стабильности.
- Документировать шаги трансформации.
- Настроить обновление источников.
Узкий пользователь Excel (бизнес‑пользователь):
- Создать резервную копию листа.
- Применить TRIM() перед извлечением.
- Использовать Flash Fill для быстрых задач.
Критерии приёмки
- Формула корректно обрабатывает 99% реальных строк в выборке.
- Нет оставшихся ведущих/замыкающих пробелов (после TRIM).
- Обработка пустых ячеек предсказуема (пусто или заданный маркер).
- Решение документировано (короткое описание формулы/Power Query шагов).
Тестовые случаи (короткий набор)
- Стандартная строка по шаблону (ожидаемый результат).
- Отсутствующий разделитель (ожидаемый: пусто или ошибка, которую обрабатываем).
- Лишние пробелы до/после (должно работать после TRIM).
- Минимально допустимая длина строки (проверка границ).
Простая блок‑схема выбора метода
flowchart TD
A[Начало: есть столбец строк?] --> B{Есть чёткий разделитель?}
B -- Да --> C{Excel 365?}
C -- Да --> D[Использовать TEXTSPLIT / TEXTBEFORE]
C -- Нет --> E[Использовать LEFT/RIGHT/MID или Flash Fill]
B -- Нет --> F{Шаблон фиксированной позиции?}
F -- Да --> E
F -- Нет --> G[Power Query]
D --> H[Тесты]
E --> H
G --> H
H --> I[Готово]Советы по отладке формул
- Если FIND возвращает ошибку, используйте IFERROR(FIND(…), 0) и обработайте случай 0.
- Для динамических длины используйте LEN вместе с FIND.
- Убирайте невидимые символы с помощью CLEAN и лишние пробелы с помощью TRIM.
Краткое руководство по переходу на Power Query
- Данные → Получить и преобразовать (Get & Transform).
- Выберите столбец и примените «Разделить столбец» по разделителю или по позициям.
- Примените шаги очистки (Trim, Lowercase, ReplaceErrors).
- Нажмите «Закрыть и загрузить» — ваш запрос сохранится и обновится при изменении источника.
Когда использовать Power Query: повторяющиеся задачи, смешанные шаблоны, большие объёмы данных.
Часто задаваемые вопросы
В: Чем LEFT отличается от TEXTBEFORE?
A: LEFT возвращает фиксированное количество символов с левой стороны. TEXTBEFORE возвращает текст до указанного разделителя и удобнее, когда есть явный разделитель (например, «@», “,” и т.п.).
В: Как избежать ошибок, если в строке нет символа «@»?
A: Оберните FIND в IFERROR или используйте условную логику: =IFERROR(LEFT(A2, FIND(“@”, A2)-1), “”) — это вернёт пустую строку, если «@» не найден.
В: Стоит ли всегда использовать Power Query?
A: Не всегда. Для простых и единичных задач формулы быстрее. Power Query оправдан при повторяемых, массовых или сложных трансформациях.
Сводка
- LEFT, RIGHT и MID — базовые, быстрые и надёжные функции для извлечения символов. Используйте их при фиксированных позициях.
- Для разделителей и современных версий Excel удобно применять TEXTBEFORE/TEXTAFTER и TEXTSPLIT.
- Для крупных, повторяемых и сложных задач лучше Power Query.
Важно: сначала осмотрите данные — это сократит время на поиск и исправление ошибок.
