Как извлечь подстроку в Excel с помощью функций FIND() и MID()

Узнайте, как использовать MID() и FIND(), а затем объединять их для анализа подстрок из противоречивых данных.

Анализ данных — обычная задача в Excel. По большей части вы будете делать это, когда вам нужно работать с подстроками, а не со всеми исходными значениями. Например, вам может потребоваться проанализировать идентификационный номер магазина или клиента из строки транзакции, которая включает всю информацию о конкретной транзакции.

Благодаря строковым функциям Excel анализ упрощается, если исходные значения согласованы. Строковые функции, такие как MID(), должны знать, где начинается и заканчивается извлекаемая подстрока. Если эта информация неизвестна или несовместима с одним значением к другому, вам придется работать немного усерднее.

В этой статье я покажу вам, как объединить две строковые функции, MID() и FIND(), чтобы решить проблему анализа структурно противоречивых данных. Я использую Microsoft 365, но эти функции доступны в автономных версиях.

Перейти к:

Что такое строковые функции?

MID() и FIND() — строковые функции. В Excel строковые функции позволяют извлекать определенные символы, также называемые подстроками, из исходной строки. Существует несколько строковых функций:

• ЛЕВЫЙ(): Получает символы из левой части исходной строки.
• ВЕРНО(): Получает символы из правой части исходной строки.
• СРЕДНИЙ(): Получает символы из середины исходной строки.
• ЛЕН(): Возвращает количество символов в исходной строке.
• НАХОДИТЬ(): Возвращает позицию определенного символа в исходной строке.

Используя эти функции по отдельности или комбинируя их, вы можете быстро вернуть подстроку из исходной строки.

Как использовать функцию MID()

При извлечении подстроки — меньшей части исходной строки — из середины исходной строки вы можете рассмотреть возможность использования MID(), который использует два аргумента для извлечения символов из середины строки. Он использует форму:

СРЕДНИЙ()(текст, начальный_номер, число_символов)

где текст — это исходная строка, начальный_номер это позиция первого символа, который вы хотите проанализировать, и число_символов — общее количество символов, которые вы хотите проанализировать.

Давайте рассмотрим быстрый пример, используя образцы данных, показанные на рис. Рисунок А.

Рисунок А

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

1. В B2 введите следующую функцию
   = СРЕДНИЙ (A2,7,1)
2. Скопируйте его в оставшиеся ячейки набора данных.

Аргумент 7 начинает анализ с седьмого символа в A2, а 1 указывает, что функция анализирует только один символ, возвращая букву B из исходной строки в A2.

Если вы скопируете функцию в оставшиеся ячейки набора данных, вы заметите, что она часто дает сбой. Нашей первоначальной задачей было вернуть первый символ, следующий за дефисом. К сожалению, дефис не всегда находится на седьмой позиции — такая позиция противоречива.

Если бы дефис располагался последовательно, MID() работал бы. Но что делать, если подстрока, которую вы хотите извлечь, может начинаться в любом месте строки? Вот в чем хитрость: вы используете функцию НАЙТИ(), чтобы вернуть позицию дефиса, а затем используете эти результаты в качестве второго аргумента функции MID(). Итак, давайте научимся использовать FIND().

ВИДЕТЬ: 3 способа подавить 0 в Excel.

Как использовать функцию НАЙТИ()

Функция FIND() Excel анализирует подстроку, находя позицию определенного символа или строки. Эта функция использует форму

НАХОДИТЬ(найти_текст, текст, [start_num])

где найти_текст — это подстрока, которую вы ищете, текст — это исходная строка, которую вы ищете, и начальный_номер указывает символ, с которого следует начать поиск. Если этот параметр опущен, поиск всегда начинается с первого символа текста.

Теперь давайте воспользуемся функцией FIND(), чтобы вернуть позицию символа дефиса:

1. В C2 введите функцию
   = НАЙТИ («-», A2)
2. Скопируйте его в оставшиеся ячейки набора данных (Рисунок Б).

Рисунок Б

Как видите, FIND() возвращает значение, а не символ. Это значение представляет позицию найденного символа, в данном случае символа дефиса.

ВИДЕТЬ: Как анализировать данные в Microsoft Excel.

Как объединить MID() и FIND()

На данный момент мы знаем пару вещей: MID() возвращает подстроку из середины исходной строки, а FIND() возвращает позицию определенной строки в исходной строке. Если исходные данные неоднородны по структуре, но каждое значение имеет общий характер, вы можете объединить две функции для выполнения работы.

Мы уже использовали FIND() для возврата позиции дефиса в каждом значении. Теперь давайте объединим его с MID(), чтобы вернуть символ, который следует сразу за дефисом в каждом значении, следующим образом:

1. В D2 введите следующую функцию
   =MID(A2,НАЙТИ(“-“,A2)+1,1)
2. Скопируйте его в оставшиеся ячейки набора данных (Рисунок С).

Рисунок С

Теперь давайте рассмотрим, как работает эта комбинация, используя первое значение в наборе данных K2445-B2100:

=MID(A2,FIND("-",A2)+1,1)

=MID(K2445-B2100,FIND("-",K2445-B2100)+1,1)

=MID(K2445-B2100,(6+1),1)

=MID(K2445-B2100,(7),1)

=MID("B",1)

=B

Функция НАЙТИ() возвращает 6, позицию первого дефиса в K2445-B2100. Затем функция MID() использует значение 6 (плюс 1) для возврата B. Мы добавляем значение 1, чтобы переместить извлечение вправо на один символ. Мы анализируем символ справа от дефиса, а не дефис.

Благодаря FIND() символ дефиса может находиться в любом месте исходной строки, и мы все равно можем найти символ относительно него.