Excel и Google Workspace / Базовые формулы Excel
TEXTBEFORE в Excel: текст до разделителя
TEXTBEFORE возвращает часть строки, расположенную перед указанным разделителем. Формула удобна для выделения кода, названия или первого поля из записи, например для отделения «Склад-12» от «Склад-12/Москва».
Формула
TEXTBEFORE(A2,"/") возвращает текст до косой черты.
Обозначения
- $text$
- строка или ссылка на ячейку, в которой выполняется поиск, текст
- $delimiter$
- символ или строка, перед которой нужно вернуть текст, текст
- $instance_num$
- номер вхождения разделителя; отрицательное число считает справа, целое число
- $match_mode$
- режим сравнения регистра: 0 различает регистр, 1 игнорирует его, 0 или 1
- $match_end$
- считать конец исходной строки дополнительным разделителем, 0 или 1
- $if_not_found$
- значение, возвращаемое вместо ошибки при отсутствии совпадения, текст или значение
Условия применения
TEXTBEFORE требует строку и разделитель. Если instance_num не указан, используется первое вхождение слева. Для положительного номера отсчёт идёт слева направо, для отрицательного — справа налево. Разделитель сравнивается как текст; пробелы, дефисы разных типов и регистр могут менять результат. Примеры используют английские имена функций и запятые; язык функции и разделитель аргументов зависят от локали Excel. Поддержка: Microsoft 365 и Excel 2024.
Ограничения
Функция не угадывает структуру грязной строки и не удаляет пробелы автоматически. Отсутствующий разделитель обычно даёт #N/A; нулевой instance_num даёт #VALUE!, а номер, превышающий число вхождений, также приводит к #N/A. match_end=1 полезен, только если конец строки должен считаться границей.
Подробное объяснение
TEXTBEFORE отделяет левую часть записи от её суффикса. В строке «Склад-12/Москва» разделитель «/» задаёт границу: слева остаётся идентификатор склада, справа — город. Исходная ячейка при этом не меняется, а для одной исходной строки результат занимает одну ячейку.
По умолчанию Excel ищет первое вхождение. В строке «AA-BB-CC» разделитель «-» встречается дважды: instance_num=2 возвращает «AA-BB», потому что граница выбрана перед последним фрагментом. Отрицательный instance_num=-1 также считает с конца и выбирает последнее вхождение. Для строки с тремя разделителями это важнее, чем механическое извлечение первого поля.
match_mode управляет регистром разделителя: 0 различает «ID» и «id», 1 считает их одинаковыми. Это не меняет регистр результата. Параметр match_end=1 рассматривает конец text как дополнительную границу, что позволяет не получать ошибку, когда искомый разделитель ожидается, но строка уже закончилась.
if_not_found лучше задавать в отчётах, где ошибка должна быть понятной человеку: например, «разделитель не найден». Однако подмена ошибки текстом не исправляет исходную строку, поэтому сначала проверьте качество данных и наличие пробелов.
TEXTBEFORE похожа на TEXTAFTER, но возвращает сторону до границы; эти функции нельзя менять местами без изменения смысла поля. Если нужно обработать несколько разделителей, используйте точную строку-разделитель и проверяйте каждый вариант записи отдельно.
Как пользоваться формулой
- Укажите исходную ячейку, например A2, и точно определите разделитель, включая пробелы вокруг него.
- Введите =TEXTBEFORE(A2,"/") и убедитесь, что первая найденная граница соответствует смыслу поля.
- Если нужен другой фрагмент, задайте instance_num; отрицательное значение считает вхождения с правого края.
- При разных вариантах регистра выберите match_mode=1, а match_end используйте только при осознанной границе в конце строки.
- Добавьте if_not_found для понятного сообщения и отдельно проверьте строки без разделителя.
- Сверьте результат с исходной строкой посимвольно и проверьте, что сам разделитель в ответ не попал.
Историческая справка
Обработка текста в электронных таблицах развивалась от ручного выделения и функций LEFT, RIGHT, MID и FIND к специализированным функциям, которые понимают границу поля. Microsoft представила TEXTBEFORE вместе с TEXTAFTER в обновлениях Excel для Microsoft 365 и документирует их в актуальной справке функций; на странице указаны каналы Current, Monthly Enterprise и Semi-Annual, а также Excel 2024 для Windows и Mac. Современная запись добавляет к простой операции поиска параметры номера вхождения, режима регистра, конца строки и безопасного ответа при отсутствии границы. Это развитие отражает переход к регулярной очистке выгрузок: одна формула теперь обслуживает и первое, и последнее поле без вспомогательной цепочки FIND и LEFT. Исторически это не формула одного автора, а результат развития табличных текстовых функций и динамических вычислений.
Пример
В ячейке A2 записано «Склад-12/Москва». Нужно получить код склада до косой черты. Формула =TEXTBEFORE(A2,"/") находит первое вхождение «/» и возвращает «Склад-12». Проверка по строке: слева от разделителя стоят символы С, к, л, а, д, -, 1, 2; справа остаётся «Москва». Вариант =TEXTBEFORE(A2,"/",,0,0,"нет разделителя") сохраняет чувствительность к регистру и выводит заданное сообщение, если граница не найдена. Формула не удаляет пробелы внутри кода и не изменяет значение A2. При копировании формулы вниз каждая строка получает собственную границу, поэтому результат следует проверять на пустых и неполных записях.
Частая ошибка
Частая ошибка — указать « / » вместо «/» и не заметить пробелы: это другой разделитель и возможен #N/A. Нельзя ставить instance_num=0: Excel вернёт #VALUE!. Не путайте номер 2 с поиском второго символа: это второе вхождение всей строки delimiter. Отрицательный номер считает справа, а не возвращает отрицательную длину. match_mode меняет сравнение разделителя, но не преобразует регистр результата.
Практика
Задачи с решением
Последнее вхождение справа
Условие. В A3 записано «AA-BB-CC». Найдите часть до последнего дефиса формулой TEXTBEFORE с instance_num=-1.
Решение. Отсчёт -1 начинается справа: последняя граница — дефис между BB и CC. Подстановка =TEXTBEFORE(A3,"-",-1) оставляет символы до неё.
Ответ. AA-BB
Второе вхождение слева
Условие. В A4 записано «AA-BB-CC». Найдите часть до второго дефиса, используя instance_num=2.
Решение. Первый дефис отделяет AA, второй — BB от CC. Подстановка =TEXTBEFORE(A4,"-",2) возвращает всё слева от второго дефиса.
Ответ. AA-BB
Дополнительные источники
- https://support.microsoft.com/en-us/excel/functions/textbefore-function
- https://support.microsoft.com/en-us/excel/functions/textafter-function
- https://support.microsoft.com/en-us/excel/text-functions-reference
Связанные формулы
Excel и Google Workspace
TEXTJOIN: объединить текст с разделителем в Excel и Google Таблицах
TEXTJOIN: объединить текст с разделителем в Excel и Google Таблицах: формула =TEXTJOIN(", ",TRUE,A2:A5) помогает настроить рабочую формулу в таблице и проверить ссылки после копирования. В тексте есть условия, пример, ошибки и проверка результата.
Excel и Google Workspace
FIND / НАЙТИ: позиция текста с учетом регистра в Excel и Google Таблицах
FIND / НАЙТИ: позиция текста с учетом регистра в Excel и Google Таблицах: формула =FIND("-",A2) помогает привести текст к нужному регистру для единообразного справочника. В тексте есть условия, пример, ошибки и проверка результата.
Excel и Google Workspace
SEARCH / ПОИСК: позиция текста без учета регистра в Excel и Google Таблицах
SEARCH / ПОИСК: позиция текста без учета регистра в Excel и Google Таблицах: формула =SEARCH("sku",A2) помогает привести текст к нужному регистру для единообразного справочника. В тексте есть условия, пример, ошибки и проверка результата.
Excel и Google Workspace
SUBSTITUTE / ПОДСТАВИТЬ: заменить текст в Excel и Google Таблицах
SUBSTITUTE / ПОДСТАВИТЬ: заменить текст в Excel и Google Таблицах: формула =SUBSTITUTE(A2,"-","") помогает настроить рабочую формулу в таблице и проверить ссылки после копирования. В тексте есть условия, пример, ошибки и проверка результата.