В Excel есть набор ТЕКСТОВЫХ функций, которые могут творить чудеса. С помощью этих функций вы можете выполнять все виды операций с фрагментами текста и кубиками.
Одной из распространенных задач людей, работающих с текстовыми данными, является извлечение подстроки в Excel (т.е. получение psrt текста из ячейки).
К сожалению, в Excel нет функции подстроки, которая могла бы сделать это легко. Однако это все еще можно сделать с помощью текстовых формул, а также некоторых других встроенных функций Excel.
Давайте сначала взглянем на некоторые текстовые функции, которые мы будем использовать в этом руководстве.
Функции Excel TEXT
В Excel есть ряд текстовых функций, которые упростят извлечение подстроки из исходного текста в Excel. Вот текстовые функции Excel, которые мы будем использовать в этом руководстве:
- Функция ВПРАВО: извлекает указанное количество символов справа от текстовой строки.
- Функция LEFT: извлекает указанное количество символов слева от текстовой строки.
- Функция MID: извлекает указанное количество символов из указанной начальной позиции в текстовой строке.
- Функция НАЙТИ: Находит начальную позицию указанного текста в текстовой строке.
- Функция LEN: возвращает количество символов в текстовой строке.
Извлечь подстроку в Excel с помощью функций
Предположим, у вас есть набор данных, как показано ниже:
Это несколько случайных (но супергеройских) идентификаторов электронной почты (кроме моего), и в приведенных ниже примерах я покажу вам, как извлечь имя пользователя и имя домена с помощью текстовых функций в Excel.
Пример 1 - Извлечение имен пользователей из адресов электронной почты
При использовании текстовых функций важно идентифицировать шаблон (если таковой имеется). Это упрощает построение формулы. В приведенном выше случае шаблон представляет собой знак @ между именем пользователя и именем домена, и мы будем использовать его в качестве ссылки для получения имен пользователей.
Вот формула для получения имени пользователя:
= ЛЕВЫЙ (A2; НАЙТИ ("@"; A2) -1)
В приведенной выше формуле функция LEFT используется для извлечения имени пользователя путем определения позиции знака @ в идентификаторе. Это делается с помощью функции НАЙТИ, которая возвращает позицию символа @.
Например, в случае [email protected] FIND («@», A2) вернет 11, что является его позицией в текстовой строке.
Теперь мы используем функцию LEFT для извлечения 10 символов слева от строки (на один меньше значения, возвращаемого функцией LEFT).
Пример 2 - Извлечение доменного имени из идентификаторов электронной почты
Та же логика, что и в приведенном выше примере, может быть использована для получения имени домена. Небольшое отличие здесь в том, что нам нужно извлекать символы справа от текстовой строки.
Вот формула, которая сделает это:
= ВПРАВО (A2; LEN (A2) -FIND ("@"; A2))
В приведенной выше формуле мы используем ту же логику, но корректируем ее, чтобы убедиться, что мы получаем правильную строку.
Давайте снова возьмем пример с [email protected]. Функция НАЙТИ возвращает положение знака @, которое в данном случае равно 11. Теперь нам нужно извлечь все символы после @. Таким образом, мы определяем общую длину строки и вычитаем количество символов до символа @. Он дает нам количество символов, которые покрывают доменное имя справа.
Теперь мы можем просто использовать функцию ВПРАВО, чтобы получить имя домена.
Пример 3 - Извлечение доменного имени из идентификаторов электронной почты (без .com)
Чтобы извлечь подстроку из середины текстовой строки, вам необходимо определить положение маркера непосредственно перед и после подстроки.
Например, в приведенном ниже примере, чтобы получить доменное имя без части .com, маркер должен быть @ (который стоит прямо перед доменным именем) и. (что сразу после него).
Вот формула, которая извлечет только доменное имя:
= MID (A2; НАЙТИ ("@"; A2) + 1; НАЙТИ (".", A2) -НАЙТИ ("@", A2) -1)
Функция Excel MID извлекает указанное количество символов из указанной начальной позиции. В этом примере выше FIND («@», A2) +1 указывает начальную позицию (которая находится сразу после @), а FIND («.», A2) -FIND («@», A2) -1 определяет количество символов между '@‘И‘.‘
Обновлять: Один из читателей William19 упомянул, что приведенная выше формула не будет работать, если в идентификаторе электронной почты есть точка (.) (Например, [email protected]). Итак, вот формула для таких случаев:
= MID (A1, НАЙТИ ("@", A1) + 1, НАЙТИ (".", A1, НАЙТИ ("@", A1)) - НАЙТИ ("@", A1) -1)
Использование текста в столбцы для извлечения подстроки в Excel
Использование функций для извлечения подстроки в Excel имеет то преимущество, что они динамичны. Если вы измените исходный текст, формула автоматически обновит результаты.
Если вам это может не понадобиться, то использование функции «Текст в столбцы» может быть быстрым и простым способом разбить текст на подстроки на основе указанных маркеров.
Вот как это сделать:
- Выделите ячейки, в которых есть текст.
- Перейдите в Data -> Data Tools -> Text to Columns.
- На шаге 1 мастера преобразования текста в столбец выберите «С разделителями» и нажмите «Далее».
- На шаге 2 установите флажок «Другое» и введите @ в поле справа от него. Это будет наш разделитель, который Excel будет использовать для разделения текста на подстроки. Вы можете увидеть предварительный просмотр данных ниже. Щелкните Далее.
- В шаге 3 в этом случае работают общие настройки. Однако вы можете выбрать другой формат, если вы разделяете числа / даты. По умолчанию в целевой ячейке находятся исходные данные. Если вы хотите сохранить исходные данные нетронутыми, измените это на другую ячейку.
- Щелкните Готово.
Это мгновенно даст вам два набора подстрок для каждого идентификатора электронной почты, используемого в этом примере.
Если вы хотите дополнительно разделить текст (например, разделить batman.com на batman и com), повторите с ним тот же процесс.
Использование НАЙТИ и ЗАМЕНИТЬ для извлечения текста из ячейки в Excel
НАЙТИ и ЗАМЕНИТЬ могут быть мощным методом при работе с текстом в Excel. В приведенных ниже примерах вы узнаете, как использовать НАЙТИ и ЗАМЕНИТЬ с подстановочными знаками, чтобы делать удивительные вещи в Excel.
Смотрите также: Узнайте все о подстановочных знаках в Excel.
Возьмем те же примеры идентификаторов электронной почты.
Пример 1 - Извлечение имен пользователей из адресов электронной почты
Вот шаги для извлечения имен пользователей из адресов электронной почты с помощью функции поиска и замены:
- Скопируйте и вставьте исходные данные. Поскольку функция «Найти и заменить» работает и изменяет данные, к которым она применяется, лучше всего иметь резервную копию исходных данных.
- Выделите данные и перейдите в раздел «Главная» -> «Редактирование» -> «Найти и выделить» -> «Заменить» (или используйте сочетание клавиш Ctrl + H).
- В диалоговом окне «Найти и заменить» введите следующее:
- Найти то, что: @*
- Заменить на: (оставьте это поле пустым)
- Нажмите «Заменить все».
Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты. У вас будет результат, как показано ниже:
Как это работает ?? - В приведенном выше примере мы использовали комбинацию @ и *. Звездочка (*) - это подстановочный знак, обозначающий любое количество символов. Следовательно, @ * будет означать текстовую строку, которая начинается с @ и может иметь любое количество символов после нее. Например, в [email protected] @ * будет @ batman.com. Когда мы заменяем @ * пробелом, удаляются все символы после @ (включая @).
Пример 2 - Извлечение доменного имени из идентификаторов электронной почты
Используя ту же логику, вы можете изменить критерии «Найти», чтобы получить доменное имя.
Вот шаги:
- Выберите данные.
- Перейдите на главную -> Редактирование -> Найти и выбрать -> Заменить (или используйте сочетание клавиш Ctrl + H).
- В диалоговом окне «Найти и заменить» введите следующее:
- Найти то, что: *@
- Заменить на: (оставьте это поле пустым)
- Нажмите «Заменить все».
Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты. У вас будет результат, как показано ниже:
