Извлечение текста между двумя символами в Excel и Google Sheets

Полное руководство о том, как найти и извлечь текст из строки между двумя символами или словами в Excel и Google Sheets.

При работе с длинными строками часто может потребоваться извлечь из них определенную часть для более тщательного изучения. В Microsoft Excel и Google Sheets есть несколько способов получить текст между двумя символами или подстроками. В этой статье мы обсудим самые быстрые и эффективные из них.

Как извлечь текст между двумя символами в Excel

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

СРЕДНЯЯ(клеткаПОИСК(символ1, клетка) + 1, ПОИСК(char2, клетка) – ПОИСК(символ1, клетка) – 1)

Например, чтобы получить текст в круглых скобках из строки в A2, используйте следующую формулу:

=СРЕДН(A2, ПОИСК(“(“, A2)+1, ПОИСК(“)”, A2) – ПОИСК(“(“, A2) -1)

Аналогичным образом вы можете извлечь текст между фигурными скобками, квадратными скобками, угловыми скобками и так далее.

Полезный совет! Если вы имеете дело с числами и хотите, чтобы результатом было число, а не числовая строка, то дополнительно выполните какое-нибудь арифметическое действие, которое не изменит результат, например, прибавьте 0 или умножьте на 1.

Например:

=СРЕДН(A2, ПОИСК(“(“, A2)+1, ПОИСК(“)”, A2) – ПОИСК(“(“, A2) -1) *1

Обратите внимание на правильное выравнивание извлеченных значений по умолчанию в ячейках, типичное для чисел:

Как работает эта формула

В основе этой формулы лежит функция MID, которая извлекает заданное количество символов из строки, начиная с определенной позиции:

MID(текст, start_num, num_chars)

Текст это ячейка, содержащая исходную строку (A2).

Исходное положение (start_num) — это символ, который следует сразу за открывающей скобкой. Итак, вы находите позицию «(» с помощью функции ПОИСК и добавляете к ней 1:

ПОИСК(“(“, A2) +1

Чтобы выяснить, сколько символов нужно извлечь (num_chars), вы определяете положение закрывающих скобок и вычитаете из него положение открывающих скобок. Кроме того, вы вычитаете 1, чтобы исключить вторые круглые скобки:

ПОИСК(“)”, A2) – ПОИСК(“(“, A2) -1

Имея все необходимые данные, функция MID выдает именно то, что вам нужно — в нашем случае текст в скобках:

СРЕДНЯЯ(A2, 19, 2)

Поскольку MID является текстовой функцией, она всегда создает строку, даже если извлечение включает только числа. Чтобы получить число в качестве конечного результата, мы умножаем вывод MID на единицу или добавляем к нему ноль. Если вы извлекаете текст, эта операция не требуется.

Извлечь текст между двумя строками/словами в Excel

Чтобы втянуть текст между двумя строками или словами, формула очень похожа на ту, что обсуждалась выше. Требуется только пара различных настроек: чтобы удалить слова-разделители из конечного результата, вы добавляете и вычитаете длину самих слов, возвращаемых функцией LEN:

СРЕДНЯЯ(клеткаПОИСК(слово1, клетка) + ДЛСТР(слово1), ПОИСК(слово2, клетка) – ПОИСК(слово1, клетка) – ЛЕН(слово1))

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

=ЕСЛИОШИБКА(СРЕДН(A2, ПОИСК(“начало”, A2) + ДЛСТР(“начало”), ПОИСК(“конец”, A2) – ПОИСК(“начало”, A2) – ДЛСТР(“начало”)), ” “)

Советы:

Чтобы избежать начальных и конечных пробелов в результатах, включите пробел после слова 1, такого как «начало», и перед словом 2, таким как «конец». Кроме того, вы можете использовать функцию TRIM, чтобы избавиться от лишних пробелов.
Если одно или оба указанных слова не найдены в исходной строке, формула вернет ошибку #ЗНАЧ! ошибка. Чтобы перехватить эту ошибку и заменить ее пустой строкой (“”), используйте функцию ЕСЛИОШИБКА, как показано в приведенном выше примере.

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

Общая формула для извлечения текста из строки между двумя вхождениями одного и того же символа:

СРЕДНЯЯ(клеткаПОИСК(уголь, клетка) +1, ПОИСК (уголь, клеткаПОИСК (уголь, клетка) +1) – ПОИСК (уголь, клетка)-1)

Например, чтобы извлечь текст, заключенный в двойные кавычки, из строки в ячейке A2, введите следующую формулу в ячейке B2:

=СРЕДН(A2, ПОИСК(“”””, A2) +1, ПОИСК(“”””, A2, ПОИСК(“”””,A2) +1) – ПОИСК(“”””, A2) -1 )

Обратите внимание на то, как вы ищете двойную кавычку в Excel. Между внешними кавычками вводится еще один набор кавычек. Первая кавычка используется, чтобы избежать особого значения второй кавычки, так что “” между крайними кавычками означает одинарную двойную кавычку.

Другой способ поставить двойную кавычку (“”) в формулу Excel — использовать функцию СИМВОЛ с кодовым номером 34.

=MID(A2, ПОИСК(СИМВОЛ(34), A2) +1, ПОИСК(СИМВОЛ(34), A2, ПОИСК(СИМВОЛ(34),A2) +1) – ПОИСК(СИМВОЛ(34), A2) -1 )

Как работает эта формула

Первые два аргумента этой формулы MID не вызывают вопросов:

A2 — это текстовая строка для поиска, а
SEARCH(“”””, A2) +1 – начальный номер, т.е. позиция первой кавычки +1.

Самая сложная часть — вычисление количества символов для извлечения (num_chars):

Во-первых, мы находим позицию второй цитаты, вложив одну функцию ПОИСК в другую.

ПОИСК(“”””, A2, ПОИСК(“”””,A2) +1)

Из позиции 2-й цитаты (в А2 это 27) вычесть позицию 1-й цитаты (в А2 это 10), а затем вычесть 1, чтобы исключить из результата саму цитату:

ПОИСК(“”””, A2, ПОИСК(“”””,A2) +1) – ПОИСК(“”””, A2) -1

Приведенная выше формула возвращает 16, что является последней недостающей частью головоломки, в которой нуждается функция MID:

=СРЕДНЕЕ(A2, 10+1, 16)

Проще говоря, MID ищет ячейку A2, начиная с символа после первой кавычки (10+1), и возвращает следующие 16 символов.

Формула Excel с учетом регистра для извлечения текста между символами

Как вы, наверное, знаете, в Microsoft Excel есть две функции для поиска строк: ПОИСК (без учета регистра) и НАЙТИ (с учетом регистра).

В ситуации, когда разделителем является буква в конкретном регистре, просто используйте НАЙТИ вместо ПОИСК. Чтобы проиллюстрировать разницу, давайте сравним две приведенные ниже формулы.

Предположим, вы хотите извлечь из строки в A2 число между двумя заглавными буквами «X».

Функция ПОИСК в этом случае работает некорректно, так как не различает «х» и «Х»:

=СРЕДН(A2, ПОИСК(“X”, A2) +1, ПОИСК(“X”, A2, ПОИСК(“X”,A2) +1) – ПОИСК(“X”, A2) -1) +0

В результате извлекается текст между «x», а не между «X», который мы ищем:

В то время как функция НАЙТИ с учетом регистра работает прекрасно:

=СРЕДН(A2, НАЙТИ(“X”, A2) +1, НАЙТИ(“X”, A2, НАЙТИ(“X”,A2) +1) – НАЙТИ(“X”, A2) -1) +0

И приносит именно тот результат, который нам нужен:

Извлечение текста между символами в Excel 365

В Excel 365 вы можете легко получить текст между символами, используя функции ТЕКСТПЕРЕД и ТЕКСТПОСЛЕ.

ТЕКСТПЕРЕД(ТЕКСТПОСЛЕ(клетка, символ1), char2)

Например, чтобы извлечь текст между скобками, формула проста:

=ТЕКСТПЕРЕД(ТЕКСТПОСЛЕ(A2, “(“), “)”)

Эта формула также хорошо работает для извлечения текста между двумя вхождениями одного и того же символа.

Например, чтобы получить текст в двойных кавычках, формула принимает следующий вид:

=ТЕКСТПЕРЕД(ТЕКСТПОСЛЕ(A2, “”””), “”””)

По умолчанию обе функции TEXTAFTER и TEXTBEFORE чувствительны к регистру. Чтобы отключить чувствительность к регистру, вы устанавливаете 4-й аргумент (match_mode) на 1 или ИСТИНА.

Например, приведенная ниже формула без учета регистра распознает как строчную букву «x», так и прописную букву «X» в качестве разделителя:

=ТЕКСТПЕРЕД(ТЕКСТПОСЛЕ(A2, “x”, ,1), “x”, ,1) +0

Как работает эта формула

Работая изнутри наружу, вы используете функцию TEXTAFTER для извлечения текста после открывающих скобок:

ТЕКСТ ПОСЛЕ(A2, “(“)

И передать возвращенную подстроку функции TEXTBEFORE, попросив ее найти закрывающие круглые скобки в этой подстроке и вернуть текст перед ней.

TEXTBEFORE(“Непредвиденная ошибка)”, “)”)

Сделанный 🙂

Получить текст между двумя символами в Google Sheets

Чтобы найти текст между двумя символами в Google Sheets, вам не нужно изобретать велосипед 🙂

Комбинации MID SEARCH, используемые в Excel, безупречно работают и в электронных таблицах Google.

Например, чтобы получить текст в квадратных скобках, используйте следующую формулу:

=СРЕДН(A2, ПОИСК(“[“, A2)+1, SEARCH(“]”, А2) – ПОИСК(“[“А2)-1)

Чтобы извлечь текст между двумя запятыми, используйте следующую формулу:

Вот как можно извлечь текст между двумя символами или словами в электронных таблицах Microsoft Excel и Google. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!