извлечь текст после символа или слова
При работе со сложными наборами данных в Excel вы часто можете оказаться в ситуации, когда вам нужно извлечь данные справа от определенного символа или слова. Этот учебник покажет вам самый быстрый способ, используя функцию Span-new TEXTAFTER.
Функция ТЕКСТАФТЕР в Excel
Функция Excel TEXTAFTER возвращает текст, который появляется после заданного символа или подстроки, которая называется разделителем. Если существует несколько вхождений разделителя, вы можете выбрать целевое вхождение. Если разделитель не найден, функция может вернуть ошибку #Н/Д, ваш собственный текст или исходную строку.
Синтаксис TEXTAFTER следующий:
ТЕКСТ ПОСЛЕ (текст, разделитель, [instance_num], [match_mode], [match_end], [if_not_found])
Функция имеет 6 аргументов, из которых требуются только первые два.
text (обязательно) – текст для извлечения. Может быть предоставлен в виде строки или ссылки на ячейку.
разделитель (обязательно) – символ или подстрока, после которой следует выполнить извлечение.
instance_num (необязательный) — экземпляр разделителя, после которого извлекается текст. Значение по умолчанию — 1. Отрицательное число начинает поиск с конца исходного текста.
match_mode (необязательный) — определяет регистрозависимость разделителя. Включено по умолчанию.
- 0 (по умолчанию) — с учетом регистра
- 1 – без учета регистра
match_end (необязательно) — обрабатывает конец текст в качестве разделитель. Отключено по умолчанию.
- 0 (по умолчанию) — соответствует разделителю точно так, как указано в формуле.
- 1 – считать конец текст в качестве разделитель. Вот как это работает на практике: если оба match_end а также instance_num равны 1, Excel возвращает пустую строку, если разделитель не найден. Если match_end равно 1 и instance_num равен -1, возвращается вся строка, если разделитель не найден.
if_not_found (необязательно) — возвращаемое значение, если разделитель отсутствует в исходном тексте. Если не задано, возвращается ошибка #Н/Д.
TEXTAFTER доступность
Функция ТЕКСТАФТЕР доступна только в Excel для Microsoft 365 (Windows и Mac) и Excel для Интернета.
Основной текст Excel после формулы
Прежде всего, давайте узнаем, как построить формулу Excel TEXTAFTER в ее простейшей форме, используя только необходимые аргументы (первые два).
Формула Excel: извлечь текст после символа
Чтобы извлечь текст, который появляется после определенного символа, вы предоставляете ссылку на ячейку, содержащую исходный текст для первого (текст) аргумент и символ в двойных кавычках для второго (разделитель) аргумент.
Например, для извлечения текста после пробела используется следующая формула:
=ТЕКСТПОСЛЕ(A2, ” “)
Формула Excel: получить текст после строки
Чтобы вернуть текст, следующий за определенной подстрокой, используйте эту подстроку в качестве разделителя.
Например, если фамилия и имя разделены запятой и пробелом, используйте строку “, ” для разделитель:
=ТЕКСТПОСЛЕ(A2, “, “)
Формула Excel: извлечь текст после слова
В терминах Excel «слово» — это не что иное, как «подстрока». Итак, чтобы скопировать текст после определенного слова в другую ячейку, укажите это слово в качестве разделителя.
Например, чтобы получить текст после слова «цвет», используйте эту формулу:
=ТЕКСТПОСЛЕ(A2, “цвет”)
Обратите внимание, что разделитель включает пробел после слова «цвет», чтобы избежать начальных пробелов в результатах.
Теперь, когда вы поняли основы, давайте рассмотрим несколько более сложных сценариев.
Вернуть текст после N-го вхождения разделителя
Чтобы получить текст, который появляется после n-го экземпляра разделителя, используйте instance_num параметр.
Например, чтобы извлечь текст после второй запятой, вы используете строку “,” для разделитель и цифра 2 для instance_num:
=ТЕКСТПОСЛЕ(A2, “, “, 2)
Опять же, разделитель включает пробел, поскольку в исходном тексте значения разделяются запятой и пробелом.
Получить текст после последнего вхождения разделителя
Чтобы извлечь текст после последнего появления разделителя, используйте отрицательное значение для instance_num аргумент.
Например, чтобы вернуть текст после последней запятой в A2, установите instance_num до -1:
=ТЕКСТПОСЛЕ(A2, “,”, “, -1)
Чтобы извлечь текст после предпоследней запятой, установите instance_num до -2:
=ТЕКСТПОСЛЕ(A2, “,”, “, -2)
Получить текст после нескольких разделителей
Чтобы обрабатывать несколько разделителей одновременно, вы можете использовать константу массива, например {“x”,”y”,”z”} в формуле TEXTAFTER, где Икс, у, а также г являются разными символами или подстроками.
Например, в наборе данных ниже разделителем является либо запятая, либо точка с запятой, с пробелом или без него. Чтобы правильно обрабатывать все четыре варианта разделителя, мы используем константу массива {“,”,”, “,”;”,”; “}:
=ТЕКСТПОСЛЕ(A2, {“,”,”, “,”;”,”; “})
Если разделитель не найден, вернуть собственный текст
Если функция ТЕКСТАФТЕР не может сопоставить разделитель в исходной текстовой строке, она по умолчанию возвращает ошибку #Н/Д. Во многих ситуациях имеет смысл вместо этого выводить пользовательское значение. Для этого необходимо определить если_не_найдено аргумент (последний).
Например, если слово «цвет» не существует в ячейке A2, эта формула выдаст ошибку #Н/Д:
=ТЕКСТПОСЛЕ(A2, “цвет”)
Чтобы вместо этого вернуть пустую ячейку, мы указываем пустую строку (“”) для если_не_найдено:
=ТЕКСТПОСЛЕ(A2, “цвет”, , , , “”)
Кроме того, вы можете ввести любой текст или символ, например, дефис “-“:
=ТЕКСТПОСЛЕ(A2, “цвет”, , , , “-“)
Если разделитель не найден, вернуть исходный текст
Чтобы возвращать исходный текст каждый раз, когда функция TEXTAFTER не может сопоставить разделитель, вам нужно сделать следующее:
- Установлен instance_num на -1 для поиска исходной строки справа налево.
- Установлен match_end на 1, чтобы рассматривать конец строки как разделитель.
При поиске в обратном направлении начало строки становится «концом», поэтому формула возвращает весь текст, если разделитель не найден.
В нашем случае разделителем является подстрока, состоящая из двоеточия и пробела:
=ТЕКСТПОСЛЕ(A2, “,”: “, -1, ,1)
Если разделитель отсутствует в данной ячейке, возвращается все содержимое ячейки.
Формула TEXTAFTER с учетом регистра и без учета регистра
По умолчанию функция Excel TEXTAFTER чувствительна к регистру, рассматривая строчные и прописные разделители как разные. Чтобы сделать формулу нечувствительной к регистру, установите match_mode аргумент равен 1 или TRUE.
Например, в приведенной ниже формуле в качестве разделителя принимается только строчная буква «цвет»:
=ТЕКСТПОСЛЕ(A2, “цвет”)
Чтобы сделать формулу нечувствительной к регистру, чтобы она принимала разделитель слов независимо от регистра букв, мы устанавливаем match_mode аргумент 1:
=ТЕКСТПОСЛЕ(A2, “цвет”, ,1)
Функция Excel TEXTAFTER не работает
Если функция не существует в вашем Excel, пожалуйста, проверьте TEXTAFTER доступность.
Если функция приводит к ошибке, это, скорее всего, одна из следующих причин:
Ошибка #Н/Д возникает, когда:
- Не найден разделитель исходной строки. Чтобы исправить ошибку, настройте если_не_найдено аргумент, как описано в этот пример.
- instance_num значение больше, чем общее количество разделителей в текст.
ЦЕННОСТЬ! ошибка возникает, когда:
- instance_num аргумент установлен в 0.
- instance_num значение превышает общую длину текст.
Вот как можно использовать функцию TEXTAFTER для извлечения текста после определенного символа или слова в Excel 365. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Функция TEXTAFTER – примеры формул (файл .xlsx)
