извлечь текст после символа или слова

При работе со сложными наборами данных в 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 для извлечения текста после пробела

Формула Excel: получить текст после строки

Чтобы вернуть текст, следующий за определенной подстрокой, используйте эту подстроку в качестве разделителя.

Например, если фамилия и имя разделены запятой и пробелом, используйте строку «, » для разделитель:

=ТЕКСТПОСЛЕ(A2, «, «)
Формула Excel для извлечения текста после подстроки

Формула Excel: извлечь текст после слова

В терминах Excel «слово» — это не что иное, как «подстрока». Итак, чтобы скопировать текст после определенного слова в другую ячейку, укажите это слово в качестве разделителя.

Например, чтобы получить текст после слова «цвет», используйте эту формулу:

=ТЕКСТПОСЛЕ(A2, «цвет»)

Обратите внимание, что разделитель включает пробел после слова «цвет», чтобы избежать начальных пробелов в результатах.
Формула Excel для получения текста после определенного слова

Теперь, когда вы поняли основы, давайте рассмотрим несколько более сложных сценариев.

Вернуть текст после N-го вхождения разделителя

Чтобы получить текст, который появляется после n-го экземпляра разделителя, используйте instance_num параметр.

Например, чтобы извлечь текст после второй запятой, вы используете строку «,» для разделитель и цифра 2 для instance_num:

=ТЕКСТПОСЛЕ(A2, «, «, 2)

Опять же, разделитель включает пробел, поскольку в исходном тексте значения разделяются запятой и пробелом.
Возвращает текст после n-го экземпляра разделителя.

Получить текст после последнего вхождения разделителя

Чтобы извлечь текст после последнего появления разделителя, используйте отрицательное значение для 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 возвращает исходный текст.

Формула TEXTAFTER с учетом регистра и без учета регистра

По умолчанию функция Excel TEXTAFTER чувствительна к регистру, рассматривая строчные и прописные разделители как разные. Чтобы сделать формулу нечувствительной к регистру, установите match_mode аргумент равен 1 или TRUE.

Например, в приведенной ниже формуле в качестве разделителя принимается только строчная буква «цвет»:

=ТЕКСТПОСЛЕ(A2, «цвет»)
Формула TEXTAFTER с учетом регистра

Чтобы сделать формулу нечувствительной к регистру, чтобы она принимала разделитель слов независимо от регистра букв, мы устанавливаем match_mode аргумент 1:

=ТЕКСТПОСЛЕ(A2, «цвет», ,1)
Формула TEXTAFTER без учета регистра

Функция Excel TEXTAFTER не работает

Если функция не существует в вашем Excel, пожалуйста, проверьте TEXTAFTER доступность.

Если функция приводит к ошибке, это, скорее всего, одна из следующих причин:

Ошибка #Н/Д возникает, когда:

  • Не найден разделитель исходной строки. Чтобы исправить ошибку, настройте если_не_найдено аргумент, как описано в этот пример.
  • instance_num значение больше, чем общее количество разделителей в текст.

ЦЕННОСТЬ! ошибка возникает, когда:

  • instance_num аргумент установлен в 0.
  • instance_num значение превышает общую длину текст.

Вот как можно использовать функцию TEXTAFTER для извлечения текста после определенного символа или слова в Excel 365. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Функция TEXTAFTER – примеры формул (файл .xlsx)

Вас также могут заинтересовать:

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *