Функция Excel TEXTBEFORE – извлечь текст перед символом (разделителем)
В этом руководстве мы рассмотрим функцию Excel TEXTBEFORE и то, как она может помочь вам быстро извлечь текст перед любым символом или подстрокой, которую вы укажете.
В более ранних версиях Excel извлечение текста перед пробелом, запятой или каким-либо другим символом было довольно сложной задачей. Сначала нужно было определить позицию разделителя в строке с помощью функции ПОИСК или НАЙТИ, а затем получить все символы перед ним с помощью ЛЕВЫЙ. В Excel 365 и Excel для Интернета теперь есть совершенно новая функция TEXTBEFORE, которая творит чудеса!
Функция Excel ПЕРЕД ТЕКСТ
Функция TEXTBEFORE в Excel специально разработана для возврата текста, стоящего перед заданным символом или подстрокой (разделителем). Если разделитель появляется в ячейке несколько раз, функция может вернуть текст перед определенным вхождением. Если разделитель не найден, вы можете вернуть свой собственный текст или исходную строку.
Синтаксис функции TEXTBEFORE имеет 6 аргументов, из которых требуются только первые два.
TEXTBEFORE(текст, разделитель, [instance_num], [match_mode], [match_end], [if_not_found])
text (обязательно) — исходный текст для извлечения. Может быть предоставлен в виде строки или ссылки на ячейку.
разделитель (обязательно) — символ или подстрока, разделяющая текст.
instance_num (необязательный) — экземпляр разделителя, перед которым извлекать текст. По умолчанию 1. Отрицательное число начинает поиск с конца исходного текста.
match_mode (необязательный) — определяет чувствительность разделителя к регистру. Включено по умолчанию.
- 0 (по умолчанию) — с учетом регистра
- 1 – без учета регистра
match_end (необязательно) — обрабатывает конец текст в качестве разделитель. Отключено по умолчанию.
- 0 (по умолчанию) — соответствует разделителю точно так, как указано в формуле.
- 1 – сопоставить разделитель с концом текст. Практически это означает, что если разделитель не найден, функция вернет исходный текст.
if_not_found (необязательно) — возвращаемое значение, если разделитель не найден. Если не задано, возвращается ошибка #Н/Д.
ТЕКСТДО доступности
В настоящее время функция ТЕКСТПЕРЕД доступна только в Excel для Microsoft 365, Excel 365 для Mac и Excel для Интернета.
Формула Excel для извлечения текста перед символом
Для начала давайте узнаем, как построить формулу TEXTBEFORE в ее простейшей форме.
Предположим, у вас есть список полных имен в столбце A и вы хотите извлечь первое имя, которое появляется перед запятой.
Это можно сделать с помощью этой базовой формулы:
=ТЕКСТПЕРЕД(A2,”,””)
Где A2 — исходная текстовая строка, а запятая (“,”) — разделитель.
Извлечь текст перед первым пробелом в Excel
Чтобы получить текст перед пробелом в строке, просто используйте символ пробела в качестве разделителя (” “).
=ТЕКСТПЕРЕД(A2,” “)
Поскольку instance_num аргумент установлен на 1 по умолчанию, формула будет возвращать текст, который появляется до первого пробела.
Кончик. Чтобы извлечь текст перед N-м пробелом, определите номер вхождения, используя 3-й (необязательно) instance_num аргумент.
Получить текст перед N-м экземпляром разделителя
Чтобы извлечь текст, который появляется перед n-м вхождением разделителя, укажите число для instance_num параметр.
Например, чтобы получить текст до второго вхождения запятой, используйте следующую формулу:
=ТЕКСТПЕРЕД(A2, “,”, 2)
Кончик. Если указанное вхождение не найдено, TEXTBEFORE возвращает ошибку #Н/Д. Вместо этого вы можете настроить если_не_найдено аргумент для возврата текста, который вы указываете или устанавливаете match_end на 1 для вывода исходного текста.
Вернуть текст перед последним вхождением разделителя
Чтобы вернуть текст перед последним вхождением указанного символа, поместите отрицательное значение в поле instance_num аргумент.
Например, чтобы вернуть текст перед последней запятой в A2, используйте следующую формулу:
=ТЕКСТПЕРЕД(A2, “,”, -1)
Чтобы извлечь текст перед предпоследней запятой, установите instance_num до -2:
=ТЕКСТПЕРЕД(A2, “,”, -2)
Извлечь текст перед подстрокой
разделитель Аргумент TEXTBEFORE может быть подстрокой, т.е. последовательностью символов, что еще больше увеличивает значение функции.
Допустим, у вас есть список имен сотрудников и их должностей в одной колонке, разделенных дефисом. Проблема в том, что некоторые имена написаны через дефис. Чтобы избежать разделения таких имен, мы используем подстроку, состоящую из пробела и дефиса (“-“) в качестве разделителя:
=ТЕКСТПЕРЕД(A2, “-“)
Получить текст перед несколькими разделителями
Чтобы обрабатывать различные варианты разделителя, вы можете использовать константу массива, такую как {“a”, “b”, “c”} в вашей формуле TEXTBEFORE, где а, б, а также с представляют разные разделители.
Например, если разделителем является запятая или дефис с предшествующим пробелом или без него, вы можете правильно обрабатывать все четыре варианта, указав константу массива {“,”,” ,”,”-“,” -” } за разделитель:
=ТЕКСТПЕРЕД(A2, {“,”,” “,”-“,”-“})
Если разделитель не найден, вернуть пользовательский текст
Если указанный разделитель не найден, функция TEXTBEFORE по умолчанию возвращает ошибку #Н/Д. Чтобы вместо этого вернуть пользовательское значение, настройте если_не_найдено аргумент (последний).
Например, если в исходном тексте (A2) нет ни одной запятой, эта базовая формула выдает ошибку #Н/Д:
=ТЕКСТПЕРЕД(A2,”,””)
Чтобы избавиться от ошибок, введите любой текст в последний аргумент, например, «Не найдено»:
=ТЕКСТПЕРЕД(A2, “,”, , , , “Не найдено”)
Чтобы ничего не возвращать, т.е. пустую ячейку, используйте пустую строку для если_не_найдено:
=ТЕКСТПЕРЕД(A2, “,”, , , , “”)
Если разделитель не найден, вернуть исходный текст
В некоторых ситуациях имеет смысл вернуть исходный текст, если функция TEXTBEFORE не может сопоставить разделитель в строке. Для этого достаточно установить match_end аргумент (5-й) равен 1. Это сообщит формуле обработать конец текст в качестве разделитель.
=ТЕКСТПЕРЕД(A2, “,”, , ,1)
Как вы можете видеть на снимке экрана ниже, формула теперь возвращает имя сотрудника, даже если в ячейке отсутствует разделитель (запятая).
Сделать разделитель чувствительным к регистру или нечувствительным к регистру
По умолчанию функция Excel TEXTBEFORE чувствительна к регистру, то есть она обрабатывает строчные и прописные разделители как разные. Чтобы отключить чувствительность к регистру, установите match_mode аргумент (4-й) в 1 или TRUE.
Например, приведенная ниже формула принимает только строчную букву «x» в качестве разделителя:
=ТЕКСТПЕРЕД(A2, “x”)
В то время как аналогичная формула с match_mode установлен в 1, распознает как “x”, так и “X” в качестве разделителя:
=ТЕКСТПЕРЕД(A2, “x”, ,1)
Функция TEXTBEFORE не работает
Если функция TEXTBEFORE не работает должным образом или недоступна в вашем Excel, это может быть одной из следующих причин.
Если TEXTBEFORE не существует в вашем Excel, ознакомьтесь со списком поддерживаемые версии.
Если TEXTBEFORE возвращает ошибку #Н/Д, то либо:
- Разделитель отсутствует в исходном тексте. Чтобы обработать ошибку, настройте если_не_найдено аргумент, как описано в этот пример.
- instance_num значение больше, чем количество вхождений разделителя в текст.
Если TEXTBEFORE возвращает #VALUE! ошибка, то либо:
- instance_num аргумент установлен в 0.
- instance_num значение больше, чем общая длина текст.
Вот как можно извлечь текст перед символом в Excel 365 с помощью функции TEXTBEFORE. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Функция TEXTBEFORE – примеры формул (файл .xlsx)
