Найти часть текста в ячейке Excel

Обновлено: 02.07.2024

Прежде чем мы начнем обсуждать различные методы работы с подстроками в Excel, давайте уделим немного времени определению термина, чтобы мы могли начать с той же страницы. Итак, что такое подстрока? Проще говоря, это часть текстовой записи. Например, если вы введете в ячейку что-то вроде "AA-111", вы назовете ее буквенно-цифровой строкой, а любая часть строки, скажем, "AA", будет подстрока.

Хотя в Excel нет такой функции, как функция "Подстрока", существуют три функции "Текст" (ЛЕВАЯ, ПРАВАЯ и СРЕДНЯЯ) для извлечения подстроки заданной длины. Кроме того, есть функции НАЙТИ и ПОИСК для получения подстроки до или после определенного символа. И есть несколько других функций для выполнения более сложных операций, таких как извлечение чисел из строки, замена одной подстроки на другую, поиск частичного совпадения текста и т. д. Ниже вы найдете примеры формул для выполнения всего этого и многого другого. .

Как извлечь подстроку определенной длины

Microsoft Excel предоставляет три различные функции для извлечения текста заданной длины из ячейки. В зависимости от того, где вы хотите начать извлечение, используйте одну из следующих формул:

- извлечь подстроку слева. - извлечь текст справа. - для извлечения подстроки из середины текстовой строки, начиная с указанной вами точки.

Как и в случае с другими формулами, функции работы с подстроками Excel лучше всего изучать на примере, поэтому давайте рассмотрим несколько из них.

Извлечь подстроку из начала строки (СЛЕВА)

Чтобы извлечь текст слева от строки, используйте функцию Excel НАЛЕВО:

Где text — это адрес ячейки, содержащей исходную строку, а num_chars — количество символов, которые вы хотите извлечь.

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

Получить подстроку с конца строки (ВПРАВО)

Чтобы получить подстроку из правой части текстовой строки, используйте функцию Excel ПРАВИЛЬНО:

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

Извлечь текст из середины строки (MID)

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

По сравнению с двумя другими текстовыми функциями, MID имеет несколько иной синтаксис:

Помимо text (исходная текстовая строка) и num_chars (количество символов для извлечения), вы также указываете start_num (значение отправная точка).

В нашем примере набора данных, чтобы получить три символа из середины строки, начинающейся с 6-го символа, вы используете следующую формулу:

Совет. Вывод формул Right, Left и Mid всегда является текстом, даже если вы извлекаете число из текстовой строки. Если вы хотите оперировать результатом как числом, оберните формулу функцией ЗНАЧЕНИЕ следующим образом: =ЗНАЧ(СРЕДН(A2,6,3))

Извлечь подстроку до или после заданного символа

Как показано в приведенных выше примерах, функции Left, Right и Mid прекрасно справляются с однородными строками. Когда вы имеете дело с текстовыми строками переменной длины, потребуются более сложные манипуляции.

Примечание. Во всех приведенных ниже примерах мы будем использовать функцию SEARCH без учета регистра, чтобы получить позицию символа. Если вам нужна формула с учетом регистра, используйте функцию НАЙТИ.

Как извлечь текст перед определенным символом

Чтобы получить подстроку, предшествующую заданному символу, необходимо сделать две вещи: во-первых, определить позицию интересующего символа, а затем вытянуть все символы перед ним. Точнее, вы используете функцию ПОИСК, чтобы найти положение символа, и вычитаете 1 из результата, потому что вы не хотите включать сам символ в вывод. Затем вы отправляете возвращенное число непосредственно в аргумент num_chars функции LEFT:

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

Независимо от того, сколько символов содержит ваша строка Excel, формула извлекает только текст до первого дефиса:

Как извлечь текст после символа

Чтобы получить текст после определенного символа, вы используете несколько иной подход: получите позицию символа с помощью ПОИСК или НАЙТИ, вычтите это число из общей длины строки, возвращаемой функцией ДЛСТР, и извлеките это количество символов из конец строки.

В нашем примере мы будем использовать следующую формулу для извлечения подстроки после первого дефиса:

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

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

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

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

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

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

Во-первых, вы определяете положение второго вхождения символа, вкладывая одну функцию поиска в другую.
После этого вы вычитаете позицию 1-го вхождения из позиции 2-го вхождения и вычитаете 1 из результата, так как вы не хотите включать символ-разделитель в результирующую подстроку.

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

=MID(A2, ПОИСК("-",A2) + 1, ПОИСК("-",A2,ПОИСК("-",A2)+1) - ПОИСК("-",A2) - 1)

На снимке экрана ниже показан результат:

Если вы хотите извлечь текст между 2-м и 3-м или 3-м и 4-м вхождением одного и того же символа, вы можете использовать более компактную комбинацию ПОДСТАВКИ ПОИСКА, чтобы получить позицию символа, как описано в разделе Как найти N-й появление символа в строке:

В нашем случае мы могли бы извлечь подстроку между 2-м и 3-м дефисом по следующей формуле:

=MID(A2, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,"-",СИМВОЛ(1),2))+1, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,"-",СИМВОЛ (1),3)) - НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(A2,"-",СИМВОЛ(1),2))-1)

Как найти подстроку в Excel

В ситуациях, когда вы не хотите извлекать подстроку, а хотите только найти ячейки, содержащие ее, вы используете функцию ПОИСК или НАЙТИ, как показано в приведенных выше примерах, но выполняете поиск в рамках функции ЕЧИСЛО. Если ячейка содержит подстроку, функция поиска возвращает позицию первого символа, и если ISNUMBER получает какое-либо число, она возвращает TRUE. Если подстрока не найдена, поиск приводит к ошибке, заставляя ISNUMBER возвращать FALSE.

Предположим, у вас есть список британских почтовых индексов в столбце A, и вы хотите найти те, которые содержат подстроку "1ZZ". Для этого используйте следующую формулу:

Результаты будут выглядеть примерно так:

Если вы хотите вернуть собственное сообщение вместо логических значений ИСТИНА и ЛОЖЬ, вложите приведенную выше формулу в функцию ЕСЛИ:

=IF(ISNUMBER(ПОИСК("1zz", A2)), "Да", "")

Если ячейка содержит подстроку, формула возвращает "Да", пустую строку (""), в противном случае:

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

Дополнительную информацию о том, как найти текст и числа в Excel, см. в разделе Если ячейка содержит примеры формул.

Как извлечь текст из ячейки с помощью Ultimate Suite for Excel

Как вы только что видели, Microsoft Excel предоставляет множество различных функций для работы с текстовыми строками. Если вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте задание нашему Ultimate Suite for Excel. Имея эти инструменты в своем арсенале Excel, просто перейдите на вкладку Данные Ablebits > группу Текст и нажмите Извлечь:

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

Укажите, сколько символов вы хотите получить от начала, конца или середины строки; или выберите извлечение всего текста до или после заданного символа.
Нажмите Вставить результаты. Готово!

Например, чтобы извлечь доменные имена из списка адресов электронной почты, выберите переключатель Все после текста и введите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все перед текстом, как показано на снимке экрана ниже.

И через мгновение вы получите следующие результаты:

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции работы с подстроками в частности. Как? Установив флажок Вставить как формулу в нижней части панели, вы гарантируете, что результаты выводятся в виде формул, а не значений.

В этом примере, если вы выберите ячейки B2 и C2, вы увидите следующие формулы соответственно:

Сколько времени вам потребуется, чтобы самостоятельно вычислить эти формулы? ;)

Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные строки будут внесены какие-либо изменения. Когда в набор данных добавляются новые записи, вы можете копировать формулы в другие ячейки, как обычно, без необходимости заново запускать инструмент «Извлечь текст».

Если вам интересно попробовать это, а также многие другие полезные функции, включенные в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию.

Дополнительные формулы для подстрок в Excel

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

- примеры формул для получения числа из различных текстовых строк. - как использовать MID в сочетании с 4 другими функциями, чтобы получить любое N-е слово из ячейки. - еще одна нетривиальная формула MID, которая извлекает слово, содержащее определенный символ (символы), из любой точки исходной строки. - используя этот подход, вы можете получить подстроку между любыми двумя указанными вами символами. - как использовать функцию подстроки Excel (LEFT, RIGHT или MID) в сочетании с Vlookup для извлечения значений на основе частичного совпадения. - формула для поиска и замены определенного символа или подстроки в текстовой строке. - как использовать функцию СЧЕТЕСЛИ с подстановочными знаками для подсчета ячеек, содержащих определенный текст, т.е. подсчет с частичным совпадением. - как извлекать текст и специальные символы с помощью регулярных выражений.

Доступные загрузки

215 комментариев к "Подстрока Excel: как извлечь текст из ячейки"

У меня есть данные в этом формате на листе Excel.

Мне нужен результат в виде любых данных, кроме начальных 7 символов.

Здравствуйте!
Чтобы извлечь часть текста, начинающуюся с определенной позиции, используйте функцию MID -

Буду признателен за любую помощь. У меня есть большой объем следующих данных (расположенных в одной ячейке);

"Выигрышная комбинация: 2/1/1/1/1/2/1/1/3/1
Статус: Официальный
Результаты: (9/10)
Победители: 2015.43
Дивиденды: 42,60 рандов
Результаты: (10/10)
Победители: 141,00
Дивиденды: 2 439,80 рандов"

Мне нужно извлечь;

2015,43
42,60
141
2439,80

Здравствуйте!
Чтобы извлечь числа из текста, вы можете использовать регулярные выражения. Вы можете найти подробные инструкции и примеры в этой статье: Как извлечь подстроки в Excel с помощью регулярных выражений (Regex).

Вы можете помочь? Я могу найти только инструкции по извлечению а) после одного символа (не строки) и никаких инструкций по указанию длины извлекаемой строки.
Спасибо!!

Здравствуйте!
Используйте следующую формулу -

В статье выше есть вся необходимая информация. Надеюсь, это будет полезно.

Спасибо за это!! Я искал что-то подобное.

Здравствуйте!
Чтобы заменить сообщение об ошибке значением, используйте функцию ЕСЛИОШИБКА.
Вы можете использовать эту формулу:

Как я могу выбрать сахар и лен из этой формулы

"Молоко, сахар, растительное масло, рапсовое масло, сухое молоко, эссенция кардамона, цельнозерновая мука, семена льна, изюм, сахар, миндаль, кешью"

Какую формулу я могу использовать для извлечения только символов в ячейке без ПОСЛЕДНИХ 4 ЦИФР
например, "Добавка витамина А 6-11 месяцев 2019" результаты должны быть "Добавка витамина А 6-11 месяцев"
>и
результаты "LLIN для беременных женщин 2021" должны быть "LLIN для беременных женщин"

Здравствуйте!
Извлечь из текста все символы, кроме последних четырех. Используйте функции ВЛЕВО и ДЛСТР.

Чтобы проверить, содержит ли ячейка определенный текст, используйте ISNUMBER и ПОИСК в Excel. В Excel нет функции СОДЕРЖАТЬ.

<р>1. Чтобы найти позицию подстроки в текстовой строке, используйте функцию ПОИСК.

Объяснение: «утка» находится на позиции 10, «осел» — на позиции 1, ячейка A4 не содержит слова «лошадь» и «коза» на позиции 12.

<р>2. Добавьте функцию ISNUMBER. Функция ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.

Объяснение: ячейка A2 содержит слово "утка", ячейка A3 содержит слово "осел", ячейка A4 не содержит слова "лошадь", а ячейка A5 содержит слово "коза".

<р>3. Вы также можете проверить, содержит ли ячейка определенный текст, не отображая подстроку. Не забудьте заключить подстроку в двойные кавычки.

<р>4. Чтобы выполнить поиск с учетом регистра, замените функцию ПОИСК на функцию НАЙТИ.

Объяснение: теперь формула в ячейке C3 возвращает FALSE. Ячейка A3 не содержит слова "осел", но содержит слово "осел".

<р>5. Добавьте функцию ЕСЛИ. Приведенная ниже формула (без учета регистра) возвращает "Найдено", если ячейка содержит определенный текст, и "Не найдено", если нет.

<р>6. Вы также можете использовать ЕСЛИ и СЧЕТЕСЛИ в Excel, чтобы проверить, содержит ли ячейка определенный текст. Однако функция СЧЁТЕСЛИ всегда нечувствительна к регистру.

Объяснение: формула в ячейке C2 сводится к =ЕСЛИ(СЧЁТЕСЛИ(A2,"*утка*"),"Найдено","Не найдено"). Звездочка (*) соответствует последовательности из нуля или более символов. Посетите нашу страницу о функции СЧЕТЕСЛИ, чтобы узнать все, что вам нужно знать об этой мощной функции.

< бр />

В Excel вам может потребоваться найти только часть текста в ячейке. Например, если у вас есть ячейка, содержащая описание транзакции, а в этом описании есть название продукта.

Вы хотите найти цену этого товара в таблице. Давайте рассмотрим три возможности:

1. Когда название продукта случайно помещается в текст поиска: «КОЛЕСО ПРОДАНО компании John’s Motors Ltd».
2. Как 1. выше, но всегда есть определенные символы до и после названия продукта: «Продано –КОЛЕСО– компании John’s Motors Ltd»
3. Название продукта всегда находится в одной и той же части строки: «AA2 WHL Sold — JM1».
Очевидно, что третью возможность в списке проще всего решить в Excel, поэтому давайте начнем с нее.

Поиск части текста в ячейке: согласованные начальная и конечная точки

Функция ВПР (или ГПР) имеет следующие аргументы: ПРОСМОТР ЗНАЧЕНИЯ , ТАБЛИЦА , ИНДЕКС НОМЕР СТОЛБЦА , ТОЧНОЕ/НЕТОЧНОЕ СОВПАДЕНИЕ . Поскольку LOOKUP VALUE — это только часть ячейки, нам нужно подумать, как мы можем извлечь нужный текст из ячейки.

Проверьте РИС.(a1) для источников LOOKUP VALUE и TABLE ARRAY .

Найти часть ячейки LOOKUP VALUE

Поскольку в этом случае начальной точкой в каждой исходной ячейке всегда является символ 5, а длина ПРОСМОТР ЗНАЧЕНИЯ всегда будет составлять 3 символа (например, «WHL» в ячейке D8), мы можем использовать функцию MID для извлечения ПОИСК ЗНАЧЕНИЕ. Для функции MID нужны ТЕКСТ, НАЧАЛЬНЫЙ НОМЕР СИМВОЛА и КОЛИЧЕСТВО СИМВОЛОВ.

В нашем примере давайте начнем с ввода функции MID в ячейку F8. Вы можете увидеть результат на РИС.(a2).

Теперь нам просто нужно использовать результат в нашей формуле ВПР. ТАБЛИЦА ПРОСМОТРА находится в диапазоне $H$8:$I$10, ИНДЕКС НОМЕРА СТОЛБЦА равен 2 (второй столбец в таблице), и в этом случае мы хотим вернуть только точные совпадения, поэтому последний аргумент будет «ЛОЖЬ».
Завершить формулу для поиска части ячейки

Поместите эту формулу в ячейку F8:

=ВПР(СРЕДНЕЕ($D8, 5, 3), $H$8:$I$10, 2, ЛОЖЬ)

Скопировав эту формулу рядом с нашими транзакциями, вы завершите поиск цен FIG(a3).

Перейдем к следующему сценарию.

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

В этом случае мы будем использовать разделитель «-» для определения начала и конца ЗНАЧЕНИЙ ПРОСМОТРА в исходных ячейках. Этот метод работает до тех пор, пока символ-разделитель используется только для идентификации названия продукта. Если появляется в другом месте в тексте, вы можете перейти к сценарию 3, который может обрабатывать ПРОСМОТР ЗНАЧЕНИЯ в любом месте ячейки.

Используйте тире для разделения Искомого значения

На рис. (b1) видно, что если мы найдем дефисы «-», мы сможем извлечь нужный текст с помощью функции MID. Для этого нам нужно будет знать начальный символ для «Колесо» и количество символов в «Колесо». Мы будем использовать функцию НАЙТИ, чтобы определить расположение наших разделителей и выполнить эти вычисления.

НАЙТИ первый разделитель

На РИС. (b2) вы можете увидеть состав функции НАЙТИ для определения начала «Колесо». Функция НАЙТИ имеет следующие аргументы: НАЙТИ ТЕКСТ, ВНУТРИ ТЕКСТА и необязательный НАЧАЛЬНЫЙ НОМЕР СИМВОЛА. Этот третий необязательный аргумент полезен, когда вы ищете второй или последующий разделитель. Мы воспользуемся им позже, а пока можем его игнорировать.

Итак, поместите нашу функцию НАЙТИ в ячейку F17. [FIG(b2)] вы можете видеть, что он вернул расположение первого тире.

НАЙТИ второй разделитель

Теперь давайте найдем второй разделитель, чтобы мы могли найти конец нашего LOOKUP VALUE. Мы собираемся сделать это, выполнив другую формулу НАЙТИ. На этот раз, однако, нам нужно будет использовать НАЧАЛЬНЫЙ НОМЕР, чтобы убедиться, что мы пропускаем разделитель, который мы уже обнаружили.

На рис. (b3) вы можете видеть, что первые два аргумента второго FIND точно такие же. Но чтобы пропустить первый разделитель, нам нужно получить номер для этого из ячейки F17 (+1, чтобы перепрыгнуть через него).

=НАЙТИ( "-", $D17, F17+1)

Используйте функцию MID, чтобы получить ПОИСК ЗНАЧЕНИЯ

Теперь у нас есть эти результаты, и мы можем составить функцию MID (как в первом сценарии).

РИС.(b4). Первый аргумент функции MID — это просто описание нашей транзакции. Во-вторых, это начальная точка, которая является нашей первой функцией НАЙТИ. Далее нам нужно количество символов для извлечения.Мы можем рассчитать это как наш второй результат НАЙТИ за вычетом первого. Вы можете видеть, что мы извлекли ПРОСМОТР.

=MID($D17, F17+1, G17-1-F17)

Прежде чем перейти к последнему шагу, давайте объединим наши функции. Вы можете сделать это, отредактировав ячейку с первым НАЙТИ и скопировав все после «=». Оставьте эту ячейку, затем отредактируйте нашу функцию MID в ячейке …. выделить ссылку на ячейку …. и вставить. Повторите это для второй функции НАЙТИ. Отлично, наша функция MID завершена.

Вы должны получить следующее:

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

ВПР часть текста

Наконец, мы можем выполнить ВПР, используя эту функцию СРЕДНЕГО СРЕДСТВА в качестве ПРОСМОТРА ЗНАЧЕНИЯ. На рисунке (b6) вы можете видеть, что мы сослались на эту ячейку, ввели нашу таблицу, выбрали второй столбец и указали, что нам нужно точное совпадение (FALSE).

=ВПР( F17 , $J$17:$K$19 , 2 , ЛОЖЬ )

Мы просто заменим ссылку на ячейку F17 формулой в этой ячейке. Скопируйте это, и все готово. FIG(b7).

=ВПР(СРЕДН($D17, НАЙТИ("-", $D17)+1, НАЙТИ("-", $D17,НАЙТИ("-",$D17)+1) -1 -НАЙТИ(" -“, $D17)) , $J$17:$K$19 , 2 , ЛОЖЬ )

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

Поиск части текста: случайный текст в ячейке

Для поиска части текста в случайно расположенной ячейке; нам понадобится что-то другое, кроме функции ВПР. Вместо этого мы собираемся создать довольно сложную ФОРМУЛУ МАССИВА, которая использует ИНДЕКС, ПОИСКПОЗ, ЕОШИБКА и НАЙТИ. Если какая-либо из этих функций вам незнакома, не волнуйтесь. Просто следуйте ему. Надеюсь, после того, как вы создали формулу массива, вы сможете понять логику этих функций.

Логика в этом решении

Давайте рассмотрим логику этого подхода для поиска части ячейки. Мы собираемся использовать то, что я назову обратным поиском. т.е. мы собираемся попробовать каждый из элементов в столбце H (первый столбец нашей таблицы поиска), чтобы увидеть, находится ли текст в этих ячейках где-нибудь в нашей исходной ячейке (описания в столбце D). Как только мы нашли подходящее, мы просмотрим соответствующие данные в столбце I и получим результат.

Хорошо, давайте начнем с просмотра, можем ли мы найти элемент в столбце H, который находится в наших исходных ячейках поиска.

Формулы МАССИВА

Формула МАССИВ позволяет нам использовать массив значений (по сравнению со стандартной эквивалентной формулой, где мы могли бы использовать только одно значение).

Возможно, вы знаете, что функция НАЙТИ возвращает ошибку, если не может найти искомый текст. Например. =FIND("C", "ABD") возвращает ошибку.

Поэтому, как часть формулы МАССИВА, функция =НАЙТИ(, «ОБРАЗЕЦ ТЕКСТА») возвращает .

Если применить это к нашим данным, получится: =НАЙТИ(, "Колесо продано John's Motors Ltd") и вернуться

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

Функция ПОИСКПОЗ В ФОРМУЛЕ МАССИВА

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

Прежде чем мы это сделаем, давайте воспользуемся функцией ЕОШИБКА для преобразования в .

Теперь вместо чисел у нас есть FALSE для поиска.

ЛОЖНЫЕ результаты — это те самородки, которые мы ищем.

Чтобы определить место FALSE, мы можем использовать функцию MATCH. Таким образом, «=ПОИСКПОЗ(ЛОЖЬ, , 0)» в формуле МАССИВА вернет 3.

Давайте пока попробуем ввести ФОРМУЛУ МАССИВА, чтобы убедиться, что она работает должным образом. Формулу см. на РИС.(c1).

Чтобы ввести формулу МАССИВА, нажмите SHIFT+CTRL+ENTER (а не просто ENTER). Это говорит Excel, что вам нужна формула массива. Вы не вводите начальный и конечный <>. Нажатие SHIFT + CTRL + ENTER вставит их. Убедитесь, что вы проверили, что они отображаются после того, как вы сделали SHIFT + CTRL + ENTER.

Вы можете видеть, что это возвращает 3, что является правильной строкой в нашей таблице для «Колесо».

Завершить поиск с помощью функции ИНДЕКС

Мы почти закончили обучение.Все, что нам нужно сделать сейчас, это использовать этот результат в функции ИНДЕКС.

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

В сумме вы можете увидеть результат на РИС.(c2). Итак, мы просматриваем нашу таблицу в столбце I по количеству строк, определенных функцией ПОИСКПОЗ.

Мы можем скопировать это, и у нас будут результаты для всех наших поисков.

На что обратить внимание

Надеюсь, вы найдете эту функцию полезной. Если у вас есть какие-либо вопросы или комментарии, оставьте их ниже.

И если вы хотите включить подстановочные знаки в "что искать", ознакомьтесь с этими примерами подстановочных знаков .

3 комментария к записи «Как найти часть текста в ячейке»

ВАУ….
ЭТО БЫЛО КРУТО…. ВЕЛИКОЛЕПНО….
Я ИСКАЛА ЭТОГО ТААААААА ДОЛГО….
БОЛЬШОЕ СПАСИБО….

Спасибо, Сэм. Я рад, что вы нашли это полезным.

Привет, спасибо! Отличный совет, очень признателен.

Оставить ответ Отменить ответ

О нас

С 1993 года я занимаюсь созданием финансовых моделей Excel, информационных панелей и автоматизации с помощью макросов.

На этом сайте вы найдете советы, учебные пособия и инструменты для бухгалтеров и финансовых специалистов.

В Excel есть набор функций для работы с текстом, которые могут творить чудеса. С помощью этих функций вы можете выполнять все виды операций с текстом и кубиками.

Одной из распространенных задач для людей, работающих с текстовыми данными, является извлечение подстроки в Excel (т. е. получение psrt текста из ячейки).

К сожалению, в Excel нет функции подстроки, которая может легко это сделать. Однако это все еще можно сделать с помощью текстовых формул, а также некоторых других встроенных функций Excel.

Это руководство охватывает:

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

Текстовые функции Excel

В Excel есть ряд текстовых функций, которые упрощают извлечение подстроки из исходного текста в Excel. Вот функции Excel Text, которые мы будем использовать в этом руководстве:
Извлечение подстроки в Excel с помощью функций

Предположим, у вас есть набор данных, как показано ниже:

Это несколько случайных (но супергеройских) идентификаторов электронной почты (кроме моего). В приведенных ниже примерах я покажу вам, как извлечь имя пользователя и доменное имя с помощью текстовых функций в Excel.

Пример 1. Извлечение имен пользователей из идентификаторов электронной почты

При использовании текстовых функций важно определить шаблон (если он есть). Это позволяет очень легко построить формулу. В приведенном выше случае шаблон представляет собой знак @ между именем пользователя и именем домена, и мы будем использовать его в качестве ссылки для получения имен пользователей.

Вот формула для получения имени пользователя:

Приведенная выше формула использует функцию LEFT для извлечения имени пользователя путем определения положения знака @ в идентификаторе. Это делается с помощью функции НАЙТИ, которая возвращает позицию @.

Теперь мы используем функцию LEFT для извлечения 10 символов слева от строки (на один меньше, чем значение, возвращаемое функцией LEFT).

Пример 2. Извлечение доменного имени из идентификаторов электронной почты

Для получения доменного имени можно использовать ту же логику, что и в приведенном выше примере. Небольшое отличие состоит в том, что нам нужно извлечь символы справа от текстовой строки.

Вот формула, которая это сделает:

В приведенной выше формуле мы используем ту же логику, но изменим ее, чтобы убедиться, что получаем правильную строку.

Теперь мы можем просто использовать функцию ПРАВИЛЬНО, чтобы получить доменное имя.

Чтобы извлечь подстроку из середины текстовой строки, необходимо определить положение маркера непосредственно перед и после подстроки.

Вот формула, которая извлечет только доменное имя:

Функция Excel MID извлекает указанное количество символов из указанной начальной позиции. В приведенном выше примере НАЙТИ("@",A2)+1 указывает начальную позицию (которая находится сразу после символа @), а НАЙТИ(".,A2)-НАЙТИ("@",A2)-1 определяет начальную позицию. количество символов между '@' и '.'

Использование текста в столбцах для извлечения подстроки в Excel

Использование функций для извлечения подстроки в Excel имеет то преимущество, что оно является динамическим. Если вы измените исходный текст, формула автоматически обновит результаты.

Если вам это не нужно, то использование функции «Текст в столбцы» может быть быстрым и простым способом разбить текст на подстроки на основе заданных маркеров.

Вот как это сделать:

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

Использование FIND и REPLACE для извлечения текста из ячейки в Excel

НАЙТИ и ЗАМЕНИТЬ может быть эффективным методом, когда вы работаете с текстом в Excel. В приведенных ниже примерах вы узнаете, как использовать функции НАЙТИ и ЗАМЕНИТЬ с подстановочными знаками, чтобы делать удивительные вещи в Excel.

Возьмем те же примеры идентификаторов электронной почты.

Пример 1. Извлечение имен пользователей из идентификаторов электронной почты

Вот шаги для извлечения имен пользователей из идентификаторов электронной почты с помощью функции «Найти и заменить»:

Это мгновенно удалит весь текст перед символом @ в идентификаторах электронной почты. Вы получите результат, как показано ниже:

Пример 2. Извлечение доменного имени из идентификаторов электронной почты

Используя ту же логику, вы можете изменить критерии «Найти что», чтобы получить доменное имя.

Вот шаги:

Это мгновенно удалит весь текст перед символом @ в идентификаторах электронной почты. Вы получите результат, как показано ниже:

Читайте также: