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

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

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

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

Перейти к:

Что такое строковые функции? Как использовать функцию MID() Как использовать функцию НАЙТИ() Как объединить MID() и FIND()

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

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

LEFT(): получает символы из левой части исходной строки. RIGHT(): получает символы из правой части исходной строки. MID(): получает символы из середины исходной строки. LEN(): возвращает количество символов в исходной строке. НАЙТИ(): возвращает позицию определенного символа в исходной строке.

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

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

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

MID()(текст, начальный_номер, число_символов)

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

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

Рисунок А

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

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

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

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

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

СМОТРИТЕ: 3 способа подавить 0 в Excel.

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

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

НАЙТИ(текст_найти, текст, [начальный_номер])

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

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

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

Рисунок Б

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

СМОТРИТЕ: Как анализировать данные в Microsoft Excel.

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

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

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

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

Рисунок С

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

=MID(A2,НАЙТИ("-",A2)+1,1)

=MID(K2445-B2100,НАЙТИ("-",K2445-B2100)+1,1)

=СРЕДНИЙ(K2445-B2100,(6+1),1)

=СРЕДНИЙ(K2445-B2100,(7),1)

= СРЕДНИЙ («Б», 1)

=Б

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

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