Как извлечь текст из ячейки с помощью функции MID и специальных инструментов
MID — одна из текстовых функций, предоставляемых Microsoft Excel для управления текстовыми строками. На самом базовом уровне он используется для извлечения подстроки из центра текста.
В этом руководстве мы обсудим синтаксис и особенности функции MID (MID) в Excel, а затем рассмотрим различные творческие варианты использования для сложных задач.
Cинтаксис
Функция MID возвращает указанное количество символов, начиная с указанной позиции.
Функция Excel MID имеет следующие аргументы:
MID (текст; начальное_число; число_символов)
Где текст — это исходная текстовая строка. Далее следует позиция первого символа, который вы хотите извлечь, и количество символов, которые нужно извлечь.
Все 3 аргумента обязательны.
Например, чтобы извлечь 6 символов из A2, начиная с 17, используйте эту формулу:
= СРЕДНИЙ (LA2; 17; 6)
Результат может выглядеть так:
5 вещей, которые вы должны знать о функции Excel ПСТР
Как вы только что видели, нет ничего плохого в использовании функции MID в Excel. Помня об этих простых фактах, вы избежите самых распространенных ошибок.
- Функция MID всегда возвращает текстовую строку, даже если извлеченная подстрока содержит только числа. Это может иметь большое значение, если вы хотите использовать результат формулы MID в других вычислениях. Чтобы преобразовать числа в числа, используйте MID вместе с функцией VALUE (английская версия), как показано в этом примере. (ссылка на последний раздел).
- Если начальная позиция больше общей длины исходного текста, формула Excel MID возвращает пробел («»).
- Если начальная позиция меньше 1, формула MID возвращает ошибку #VALUE!.
- Когда третий аргумент меньше 0 (отрицательное число), формула MID возвращает ошибку #VALUE !. Если количество символов для извлечения равно 0, выводится пустая строка (пустая ячейка).
- Если сумма начальной позиции и количества символов превышает общую длину исходного текста, функция MID в Excel возвращает подстроку от начальной позиции до последнего символа.
При решении реальных проблем в Excel очень часто вам потребуется использовать MID в сочетании с другими функциями, как показано в примерах ниже.
Как извлечь имя и фамилию.
Если у вас была возможность прочитать наши недавние руководства, вы уже знаете, как извлечь имя с помощью функции LEFT и получить фамилию с помощью RIGHT. Но, как это часто бывает в Excel, одно и то же можно сделать по-разному.
Получаем имя.
Предполагая, что полное имя находится в ячейке A2, а имя и фамилия разделены диапазоном, вы можете извлечь имя, используя следующую формулу:
= СРЕДНИЙ (A2; 1; ПОИСК («»; A2) -1)
ПОИСК используется для сканирования исходного значения на наличие пробела («») и возврата позиции, из которой вычитается 1, чтобы избежать пробелов после имени. Затем вы используете MID для возврата подстроки, начинающейся с первого символа и заканчивающейся предыдущим пробелом, таким образом извлекая имя.
Получаем фамилию.
Чтобы извлечь фамилию из A2, используйте эту формулу:
= ОБРЕЗАТЬ (СРЕДНИЙ (A2; ПОИСК («»; A2); DLSTR (A2)))
Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нет необходимости вычислять точную конечную позицию (помните, что если начальная позиция и количество комбинированных символов больше, чем общая длина текста, все остальное возвращается). Таким образом, в аргументе количества символов вы просто указываете общую начальную длину, возвращаемую функцией DLSTR. Однако вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, TRIM удаляет лишние пробелы, и вы получаете следующий результат:
Как выделить подстроку между двумя разделителями.
Продолжим предыдущий пример. А если в ячейке A2 кроме имени и фамилии есть еще и отчество, как его извлечь?
Технически задача сводится к определению положения двух пробелов в исходном тексте, и сделать это можно так:
- Как и в предыдущем примере, используйте ПОИСК, чтобы найти позицию первого («»), к которому вы добавляете 1, потому что вы хотите начать с символа, который следует за ним. Затем получите адрес начальной позиции: ПОИСК («»; A2) +1
- Затем вычислите позицию 2-го диапазона, используя вложенные функции поиска, которые сообщают Excel начать поиск именно со 2-го диапазона: ПОИСК («»; A2, ПОИСК («»; A2) +1)
Чтобы найти количество символов для извлечения, вычтите позицию первого пробела из позиции второго. А затем вычтите из результата 1, так как вам не нужны лишние пробелы в результате. Итак, у вас есть второй аргумент в пользу формулы:
ПОИСК («»; A2; ПОИСК («»; A2) +1) — ПОИСК («»; A2)
Собирая все аргументы вместе, получаем формулу для извлечения подстроки между двумя пробелами:
= MID (A2; ПОИСК («»; A2) +1; ПОИСК («»; A2, ПОИСК («»; A2) +1) -ПОИСК («», A2) -1)
На следующем снимке экрана показан результат:
Точно так же вы можете извлечь текст между любым другим разделителем:
PSTR (строка; ПОИСК (разделитель; строка) +1; ПОИСК (разделитель; строка; ПОИСК (разделитель; строка) +1) — ПОИСК (разделитель; строка) -1)
Например, чтобы извлечь сегмент, разделенный запятой, за которой следует пробел, используйте следующее выражение:
= СРЕДНИЙ (A2; ПОИСК («,», A2) +1, ПОИСК («,», A2, ПОИСК («,», A2) +1) -ПОИСК («,», A2) -1)
На следующем рисунке эта формула используется для извлечения названия города из адреса, и она хорошо справляется со своей задачей:
Как получить N-е слово из текста.
Этот пример демонстрирует первоначальное использование сложной формулы MID в Excel, которая включает 5 различных строительных блоков:
- DLSTR — чтобы получить общую длину.
- REPEAT — повторение заданного символа указанное количество раз.
- ЗАМЕНИТЬ — заменяет один символ другим.
- PSTR — извлекает подстроку.
- TRIMS — убирает лишние пробелы между словами.
Общая формула выглядит так:
TRIM (MID (SUBSTITUTE (строка; «»; REPEAT («»; DLSTR (строка))))); (N -1) * DLSTR (строка) +1; DLSTR (строка)))
Где:
- Строка — это исходный текст, из которого вы хотите извлечь нужное слово.
- N — порядковый номер полученного слова.
Например, чтобы извлечь второе слово из A2, используйте это выражение:
= ОБРЕЗАТЬ (MID (ПОДСТАВИТЬ ($ A $ 2; «»; ПОВТОР («»; DLSTR ($ A $ 2))))), (2-1)) * DLSTR ($ A $ 2) +1; DLSTR ($ A $ 2)))
Или вы можете ввести порядковый номер слова, которое вы хотите извлечь (N), в любую ячейку и указать эту ячейку в формуле, как показано ниже:
Как работает эта формула?
По сути, Excel заключает каждое слово исходного текста с множеством пробелов, находит нужные пробелы-пробелы-слова, извлекает их, а затем удаляет лишние пробелы. В частности, это работает по следующей логике:
- REPLACE и REPEAT заменяют каждый пробел в тексте дополнительными пробелами. Количество этих дополнительных вставок равно общей длине исходной строки: SUBSTITUTE ($ A $ 2; «»; REPEAT («»; DLSTR ($ A $ 2)))
Вы можете думать о промежуточном результате как о «астероидах» слов, дрейфующих в пространстве, например: word1-space-word2-space-word3 -… Эта длинная строка передается в текстовый аргумент MID.
- Затем определите начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * DLSTR (A1) +1. Это вычисление возвращает либо позицию первого символа первого слова, либо, чаще, позицию в n-й группе пробелов.
- Количество извлекаемых букв и цифр (второй аргумент) — это самая простая часть — просто возьмите исходную общую длину: DLSTR (A2).
- Наконец, TRIM устраняет начальный и конечный интервалы в извлеченном тексте.
Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если в строке между словами есть 2 или более пробелов, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию TRIM в SUBSTITUTE, чтобы удалить лишние пробелы между словами, оставив только одно, например:
= TRIM (PSTR (SUBSTITUTE (TRIM ($ A $ 2), «»; REPEAT («»; DLSTR ($ A $ 2))))); (B2-1) * DLSTR ($ A $ 2) +1 ; DLSTR ($ A $ 2)))
На следующем рисунке показана улучшенная версия (2) в действии:
Если исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, добавьте дополнительные TRIM для каждого DLSTR, чтобы обезопасить себя от ошибок:
= ОБРЕЗКА (СРЕДНЯЯ (ПОДСТАВИТЬ (ОБРЕЗКА ($ A $ 2); «»; ПОВТОР («»; DLSTR (ОБРЕЗКА ($ A $ 2)))))), (B2-1) * DLSTR (ОБРЕЗКА ($ A $ 2)) + 1; DLSTR (TRIM ($ A $ 2))))
Я согласен, что он выглядит немного загроможденным, но безупречно обрабатывает все параметры.
Извлекаем слово, содержащее определенный символ.
В этом примере показана другая нестандартная формула Excel MID, которая извлекает слово, содержащее определенную букву или цифру, из любого места:
TRIMS (MID (ПОДСТАВИТЬ (строка, «», ПОВТОР («», 99)))), МАКС (1, НАЙТИ (символ, ПОДСТАВИТЬ (строка, «», ПОВТОР («», 99))))) — 50), 99))
Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:
= SIZE (MID (REPLACE (LA2, «»; REPEAT («»; 99))); MAX (1, FIND («$»; REPLACE (LA2, «»; REPEAT («»; 99))) — 50); 99))
Точно так же вы можете извлекать адреса электронной почты (на основе знака «@»), названия веб-сайтов (на основе «www») и так далее.
Теперь приступим к шагу за шагом:
Как и в предыдущем примере, REPLACE и REPEAT преобразуют каждый пробел в исходном тексте в разные, точнее, 99.
FIND находит позицию желаемого символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций и помещает вас где-нибудь в середине блока из 99 пробелов, предшествующего слову, содержащему указанный символ.
MAX используется для обработки ситуации, когда желаемое значение появляется в начале исходного текста. В этом случае результатом SEARCH () — 50 будет отрицательное число, а MAX (1, SEARCH () — 50) заменит его на 1.
С этой начальной точки MID выбирает следующие 99 символов и возвращает интересующее слово, окруженное множеством пробелов. Как обычно, TRIM помогает избавиться от лишних, оставив только один.
Совет. Если извлекаемый сегмент очень большой, замените 99 и 50 более крупными числами, например 1000 и 500.
Как заставить ПСТР возвращать число?
Как и другие текстовые функции, Excel MID всегда возвращает текст, даже если он содержит только числа и очень похож на число. Вы можете убедиться в этом, посмотрев на приведенный выше пример, когда мы получили число «20%» в виде текста.
Чтобы преобразовать результат в число, просто передайте результат в функцию VALUE, которая преобразует текстовое значение, состоящее из чисел, в число.
Например, чтобы извлечь трехсимвольную подстроку, начинающуюся с символа 7, и преобразовать ее в число, используйте:
= ЗНАЧЕНИЕ (СРЕДНЕЕ (A2,7,3))
На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещаются в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:
Такой же подход работает даже в самых сложных случаях. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью MID, который получает подстроку между двумя вложенными разделителями в VALUE:
= ЗНАЧЕНИЕ (СРЕДНЕЕ (A2; ПОИСК («:»; A2) +1, ПОИСК («:», A2, ПОИСК («:», A2) +1) -ПОИСК («:», A2) -1))
Вот как вы можете использовать функцию MID в Excel.
Как извлечь текст из ячейки с помощью Ultimate Suite
Как вы только что видели, Microsoft Excel предоставляет ряд различных функций для работы с текстовыми строками. Если вам нужно извлечь слово или фрагмент текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, перенесите работу в Ultimate Suite for Excel. В то же время вам не придется возиться с формулами.
Просто перейдите на вкладку «Данные Ablebits»> «Текст», выберите инструмент «Разделить текст» и нажмите «Извлечь» из раскрывающегося списка) :
Теперь вы выбираете исходные ячейки, и независимо от того, насколько сложные строки они содержат, извлечение из них подстроки сводится к этим двум простым шагам:
- Укажите, сколько символов вы хотите получить с начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
- Щелкните Вставить результаты. Готовый!
Кроме того, вы можете извлекать любое количество символов из начала или конца текста, из середины текста между некоторыми символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, установите флажок Все после текста и введите @ в поле рядом с ним. Чтобы получить имена пользователей, установите переключатель «Все» перед текстом, как показано на изображении ниже.
Помимо того, что инструмент «Извлечь текст» является быстрым и простым, он имеет дополнительную ценность, помогая вам изучать формулы Excel в целом и функции подстроки в частности. Нравиться? Установив флажок «Вставить как формулу» в нижней части панели, вы убедитесь, что результаты отображаются в виде формул, а не только значений. Конечно, вы можете использовать эти формулы в других таблицах.
В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:
- Чтобы получить имя пользователя:
= ЕСЛИ ОШИБКА (ЛЕВО (LA2; ПОИСК («@»; A2) -1),»»)
- Чтобы восстановить домен:
= SEERROR (RIGHT (A2; DLSTR (A2) — ПОИСК («@»; A2) — DLSTR («@») + 1),»»)
Сколько времени вам понадобится, чтобы составить эти выражения самостоятельно?
