Если вы когда-нибудь тратили десять минут на ручное объединение ячеек или разбиение текста в Excel, вы оцените возможности функций TEXTJOIN, TEXTBEFORE и TEXTAFTER. Эти три формулы Excel ставят разделители во главу угла, позволяя объединять, разделять и извлекать текст с гораздо меньшими усилиями, чем старые методы.
Что такое разделитель в данном контексте?
Разделитель — это просто символ (или строка символов), который обозначает границу между фрагментами данных. В формулах Excel вы указываете разделитель, чтобы сообщить функции, где разрезать или где соединять. Распространённые примеры: запятые, пробелы, вертикальные черты, точки с запятой и дефисы.
Как только вы поймёте эту идею, работать с этими тремя функциями станет гораздо проще.
TEXTJOIN: объединение текста с разделителем
TEXTJOIN объединяет несколько текстовых значений в одну строку, помещая выбранный вами разделитель между каждым элементом. Синтаксис прост:
- Выберите разделитель (например, запятую и пробел).
- Установите параметр ignore_empty в TRUE или FALSE, чтобы пропускать пустые ячейки или включать их.
- Укажите диапазон или отдельные ячейки, которые нужно объединить.
Простой пример: =TEXTJOIN(", ", TRUE, A2:A6) объединяет все значения из ячеек A2–A6, разделяя их запятой и пробелом. Если ячейка A4 пуста и параметр ignore_empty установлен в TRUE, в результате не появится двойная запятая.
Совет: TEXTJOIN заменила старые обходные решения с CONCATENATE. Если вы всё ещё вручную склеиваете ячейки с помощью оператора &, переходите на TEXTJOIN и избавьте себя от лишних хлопот.
TEXTBEFORE: извлечение текста перед разделителем
TEXTBEFORE извлекает всё, что находится слева от указанного разделителя. Эта функция идеальна, когда у вас есть структурированный текст, например полные имена или пути к файлам, и вам нужен только первый сегмент.
Синтаксис: =TEXTBEFORE(text, delimiter, [instance_num]). Необязательный аргумент instance_num позволяет указать конкретное вхождение разделителя, что полезно, когда в одной ячейке несколько разделителей.
Например, если ячейка A1 содержит "London, UK, Europe", то =TEXTBEFORE(A1, ",") вернёт "London". Добавьте instance_num равным 2, и вы получите "London, UK".
TEXTAFTER: извлечение текста после разделителя
TEXTAFTER работает аналогично, но извлекает всё, что находится справа от разделителя. Используя тот же пример, =TEXTAFTER(A1, ", ") вернёт "UK, Europe". Эта функция является естественным дополнением к TEXTBEFORE.
В обеих функциях можно использовать отрицательные номера вхождений для отсчёта с конца строки, а не с начала. Это удобный приём, когда нужен последний сегмент пути или идентификатора.
Краткое сравнение
| Функция | Что делает | Пример результата |
|---|---|---|
| TEXTJOIN | Объединяет несколько значений с разделителем | "cat, dog, fish" |
| TEXTBEFORE | Возвращает текст перед разделителем | "London" из "London, UK" |
| TEXTAFTER | Возвращает текст после разделителя | "UK" из "London, UK" |
Практический пример рабочего процесса
Допустим, у вас есть столбец с адресами электронной почты и нужно отделить имя пользователя от домена. TEXTBEFORE и TEXTAFTER решают эту задачу одной формулой. =TEXTBEFORE(A2, "@") возвращает имя пользователя. =TEXTAFTER(A2, "@") возвращает домен.
Затем, если вы хотите собрать очищенный список доменов в одну ячейку, с этим справится TEXTJOIN. Эти функции естественно работают вместе как часть единого процесса очистки данных.
Внимание: TEXTBEFORE и TEXTAFTER доступны только в Excel 365 и Excel 2021 или более поздних версиях. Если у вас более старая версия, придётся использовать функции LEFT, RIGHT, MID и FIND в качестве замены.
Работа с разделителями за пределами Excel
Иногда данные появляются за пределами Excel — в виде экспорта CSV или необработанного текстового файла с непоследовательными разделителями. Прежде чем открывать Excel, может потребоваться переформатировать эти разделители. Онлайн-конвертер разделителей на Delimiter.site позволяет заменить один разделитель на другой за считанные секунды, без написания единой формулы.
Когда в вашем файле используется единый разделитель, формулы Excel, описанные выше, работают гораздо надёжнее. Принцип «мусор на входе — мусор на выходе» по-прежнему актуален.
Ключевые моменты
- TEXTJOIN объединяет диапазон ячеек с любым разделителем на ваш выбор и корректно обрабатывает пустые ячейки.
- TEXTBEFORE извлекает всё, что находится слева от разделителя, с возможностью указать конкретное вхождение.
- TEXTAFTER делает то же самое с правой стороны, и обе функции поддерживают отрицательные номера вхождений для отсчёта с конца.
- Все три функции доступны в Excel 365 и Excel 2021+, но не в более старых версиях.
- Если исходные данные содержат беспорядочные или смешанные разделители, сначала очистите их с помощью такого инструмента, как конвертер запятых в вертикальные черты, прежде чем импортировать данные в Excel.
Применяйте эти формулы на практике
TEXTJOIN, TEXTBEFORE и TEXTAFTER по-настоящему полезны, как только вы освоитесь с аргументом разделителя. Они покрывают большинство типичных задач по разделению и объединению текста без сложных вспомогательных столбцов или VBA.
Начните с небольшого набора данных, попробуйте каждую формулу один раз, и вы быстро поймёте, где они вписываются в ваш повседневный рабочий процесс. А если вам нужно конвертировать разделители онлайн перед импортом данных в Excel, этот шаг займёт столь же мало времени.