Excel извлекает подстроку из строки

Обновлено: 21.11.2024

В этом руководстве я покажу вам, как легко извлечь подстроку любого типа в Excel.

  1. Что такое подстрока?
  2. Извлечение подстроки Excel — обзор.
  3. Как извлечь подстроку Excel фиксированной длины?
    • Извлечь подстроку с левой стороны (LEFT)
    • Извлечь подстроку из середины (MID)
    • Извлечь подстроку из конца (справа)
  4. Как извлечь подстроку Excel переменной длины?
    • Как извлечь подстроку перед разделителем?
    • Как извлечь подстроку после разделителя?
    • Как извлечь подстроку между двумя разделителями?
  5. Как проверить подстроку в Excel?
  6. Как извлечь подстроку Excel, содержащую определенный текст или символ?
  7. Извлечение подстроки Excel с помощью функции преобразования текста в столбцы
  8. Извлечение подстроки Excel с помощью Flash Fill

По теме:

Что такое подстрока?

Вы когда-нибудь задумывались, как извлечь часть текстовой строки в Excel?

Прежде чем я расскажу вам, как это сделать, давайте посмотрим, что такое подстрока.

Подстрока — это не что иное, как часть любой строки. Эта строка может быть чистой текстовой строкой или даже буквенно-цифровой строкой.

Допустим, например, у меня есть набор буквенно-цифровых кодов продуктов в формате «xxxx-yyyy-zzzz». Крайний левый набор символов указывает на категорию продукта, средний набор символов указывает на региональный код, а последний набор символов указывает на код производителя.

Здесь каждый набор из четырех символов является подстрокой всего кода продукта. Например, «xxxx» — это подстрока «xxxx-yyyy-zzzz».

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

Извлечение подстроки Excel — обзор.

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

Я рассмотрю все возможные сценарии извлечения подстрок и объясню каждый из них на примерах.

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

Извлечь подстроку с левой стороны (LEFT)

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

В этом примере я ввожу =LEFT(A2,4), чтобы извлечь первые четыре символа кода продукта из его левой части.

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

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

В этом примере я ввожу =MID(A2,6,4), чтобы извлечь четыре символа, начиная с 6-го символа кода продукта.

Извлечь подстроку с конца (справа)

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

Например, я ввожу =RIGHT(A2,4), чтобы извлечь последние четыре символа кода продукта.

Также читайте:

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

В некоторых случаях все может быть не так просто. Подстрока не может иметь фиксированную длину.

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

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

Обратите внимание, что ПОИСК не чувствителен к регистру, тогда как НАЙТИ учитывает регистр.

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

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

= LEFT(cell_reference, SEARCH("Delimiter", cell_reference)-1) внутри соседней ячейки и перетащите его на весь диапазон ячеек.

Здесь «Разделитель» может быть любым символом в выбранной вами строке, а «cell_reference» — это ссылка на ячейку исходной текстовой строки.

В этой формуле функция ПОИСК ищет разделитель и возвращает его позицию в строке. Затем функция ВЛЕВО извлекает подстроку до позиции разделителя.

Примечание. Здесь функция ПОИСК возвращает позицию первого экземпляра разделителя.

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

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

ВПРАВО(ячейка_ссылка,ДЛСТР(ячейка_ссылка)-ПОИСК("Разделитель", ячейка_ссылка)) внутри соседней ячейки и перетащите ее на весь диапазон ячеек.

В этой формуле функция ПОИСК ищет разделитель и возвращает его позицию в строке. Затем функции RIGHT и LEN извлекают подстроку после позиции разделителя.

Примечание. Здесь функция ПОИСК возвращает позицию первого экземпляра разделителя. Если у вас более одного разделителя и вы хотите извлечь подстроку после последнего разделителя, проще использовать функцию преобразования текста в столбцы.

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

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

Но если вы все же хотите использовать для этого формулу, используйте это:

=MID(ссылка_ячейки, ПОИСК("Разделитель", ссылка_ячейки)+1, ПОИСК("Разделитель", ссылка_ячейки, ПОИСК("Разделитель", ссылка_ячейки)+1) – ПОИСК("Разделитель", ссылка_ячейки)-1)

Здесь функция MID имеет три аргумента: cell_reference, начальную позицию и длину строки.

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

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

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

Используйте эту формулу:

=MID (ссылка_ячейки, НАЙТИ (СИМВОЛ (1), ПОДСТАВИТЬ (ссылка_ячейки», «Разделитель», СИМВОЛ (1), N))+1, НАЙТИ (СИМВОЛ (1), ПОДСТАВИТЬ (ссылка_ячейки», «Разделитель», СИМВОЛ (1), N+1)) – НАЙТИ (СИМВОЛ (1), ПОДСТАВИТЬ (ячейка_ссылка», «Разделитель», СИМВОЛ (1), N))-1)

Где N и N+1 — n-е и n+1-е количество вхождений разделителя в исходной строке, между которыми находится интересующая подстрока.

Например, если вы хотите извлечь подстроку между 3-м и 4-м вхождением разделителя, замените N и N+1 на 3 и 4 соответственно.

Как проверить подстроку в Excel?

В некоторых случаях вам может потребоваться только проверить, присутствует ли определенная подстрока в тексте или нет. Сделать это легко с комбинацией ISNUMBER и SEARCH. Замените ПОИСК на НАЙТИ, если вам нужны результаты с учетом регистра.

Введите формулу =ISNUMBER(SEARCH("substring", cell_reference)) в соседней ячейке столбца и перетащите ее на весь диапазон данных.

Здесь функция ПОИСК ищет подстроку в тексте и возвращает числовое значение, обозначающее позицию подстроки, только если она присутствует. Возвращает ошибку, если подстрока отсутствует в тексте. Таким образом, функция ЕЧИСЛО возвращает ИСТИНА или ЛОЖЬ в зависимости от входных данных функции ПОИСК.

Как извлечь подстроку Excel, содержащую определенный текст или символ?

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

=TRIM(MID(ПОДСТАВИТЬ(ссылка_ячейки", "", ПОВТОР(" ",100)), МАКС(1, НАЙТИ("Символ", ПОДСТАВИТЬ(ссылка_ячейки", "", ПОВТОР(" ",100))) -50),100))

Здесь «Персонаж» — это текст или персонаж, который вас интересует. Числа 100 и 50 произвольны.Используйте любое большое число, которое вы хотите, при условии, что вы замените «50» половиной числа по вашему выбору.

Позвольте мне разбить эту формулу на три простых шага.

Шаг 1. Комбинация SUBSTITUTE и REPT заменяет каждый пробел сотнями пробелов.

Шаг 2. Комбинация MID и FIND ищет конкретное имя и символ, который вы ищете, и

извлекает содержащую его подстроку. Функция MAX гарантирует, что MID

функция не начинается с отрицательной позиции в случаях, когда символ присутствует в

первое слово текста.

Шаг 3. Функция TRIM удаляет лишние пробелы в извлеченной подстроке.

Примечание. Эта формула работает только при наличии пробела в качестве разделителя в текстовой строке. Если у вас есть какой-либо другой разделитель, разбейте его на два шага. Во-первых, используйте ту же формулу, что и выше, вплоть до функции MID. Затем замените TRIM на другую ЗАМЕНУ и выберите «» в качестве замены.

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

Это еще один простой способ извлечь подстроку в Excel. Просто выберите соответствующий диапазон данных и нажмите кнопку «Текст в столбцы» на вкладке «Данные». Выберите соответствующий разделитель и нажмите "ОК" в мастере "Текст в столбцы".

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

Извлечение подстроки Excel с помощью Flash Fill

Возможно, это самый простой способ извлечь подстроку в Excel. Просто вручную введите подстроку для первых нескольких экземпляров и перетащите ее в остальную часть диапазона данных. Позвольте Excel Flash Fill показать свое волшебство и заполнить все подстроки для вас.

Примечание. Этот метод не является динамическим и в некоторых случаях может не работать. Кроме того, мгновенное заполнение доступно только в версиях Excel 2013 и более поздних.

Предлагаемые чтения:

Давайте подведем итоги

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

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

Саймон Сез, ИТ-специалист, преподает Excel более десяти лет. За небольшую ежемесячную плату вы можете получить доступ к более чем 100 обучающим курсам по ИТ. Нажмите здесь, чтобы перейти к продвинутым курсам Excel с углубленными учебными модулями.

Адам Лейси

Адам Лейси – энтузиаст Excel и эксперт по онлайн-обучению. Он сочетает эти две страсти в Simon Sez IT, где он носит несколько разных шляп. Когда Адама не беспокоит посещаемость сайта или сводные таблицы, вы найдете его на теннисном корте или на кухне, готовя бурю.

Прежде чем мы начнем обсуждать различные методы работы с подстроками в 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 > группу Текст и нажмите Извлечь:

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

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

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

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

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы 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 для беременных женщин"

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

Извлечь подстроку из текстовой строки в Excel

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

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

В Excel функции ЛЕВЫЙ, ПРАВЫЙ и СРЕДНИЙ могут помочь вам извлечь подстроку заданной длины слева, справа или в середине текстовой строки.

  • Функция LEFT: извлечение подстроки слева от текстовой строки.
  • Функция RIGHT: извлечение текста справа от текстовой строки.
  • Функция MID: извлечение подстроки из середины текстовой строки.
Извлечь подстроку слева от текстовой строки:

Функция LEFT может помочь вам извлечь первые x символов из текстовой строки. Общий синтаксис:

  • text : текстовая строка, из которой вы хотите извлечь символы.
  • num_chars : количество символов, которое вы хотите извлечь из левой части текстовой строки.

Введите следующую формулу в пустую ячейку:

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

Извлечь подстроку справа от текстовой строки:

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

  • text : текстовая строка, из которой вы хотите извлечь символы.
  • num_chars : количество символов, которое вы хотите извлечь из правой части текстовой строки.

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

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

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

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

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

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

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

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

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

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

Для извлечения всех символов перед определенным символом используется общий синтаксис:

  • text : текстовая строка, из которой вы хотите извлечь символы.
  • char : конкретный символ, на основе которого извлекается подстрока.

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

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

Пояснение формулы:
  • ПОИСК("-",A2)-1 : эта функция ПОИСК используется для поиска положения символа дефиса в ячейке A2 и вычитания 1, чтобы исключить сам символ. Он распознается как аргумент num_chars функции LEFT.
  • ЛЕВЫЙ(A2, ПОИСК("-",A2)-1): эта функция ВЛЕВО извлекает количество символов, возвращаемое функцией ПОИСК, из левой части текста в ячейке A2.
Извлечь подстроку после определенного символа

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

  • text : текстовая строка, из которой вы хотите извлечь символы.
  • char : конкретный символ, на основе которого извлекается подстрока.

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

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

Пояснение формулы:
  • ПОИСК("-",A2): эта функция ПОИСК используется для поиска положения символа дефиса в ячейке A2.
  • ДЛСТР(A2)-ПОИСК("-",A2): из общей длины текстовой строки, возвращаемой функцией ДЛСТР, вычитается число, возвращаемое функцией ПОИСК, чтобы получить количество символов после определенного символа. Это распознается как аргумент num_chars функции RIGHT.
  • RINGT(): эта функция RIGHT используется для извлечения количества символов из конца текстовой строки в ячейке A2.
Извлечь подстроку между двумя определенными символами

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

  • text : текстовая строка, из которой вы хотите извлечь символы.
  • char : конкретный символ, на основе которого извлекается подстрока.

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

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

Пояснение формулы:

  • ПОИСК("-",A2) + 1: Функция ПОИСК используется для возврата позиции символа дефиса, а добавление 1 означает извлечение подстроки из следующего символа. Он распознается как аргумент start_num функции MID.
  • ПОИСК("-",A2,ПОИСК("-",A2)+1) : эта часть формулы используется для получения позиции второго вхождения символа дефиса.
  • ПОИСК("-",A2,ПОИСК("-",A2)+1) - ПОИСК("-",A2) - 1) : используйте позицию второго символа дефиса, вычтите позицию первого дефиса чтобы получить количество символов между этими двумя символами дефиса, затем вычтите 1 из результата, чтобы исключить символ-разделитель. Это распознается как аргумент num_chars функции MID.
  • MID(): эта функция MID используется для извлечения подстроки на основе двух указанных выше аргументов.

Используемые относительные функции:

  • СЛЕВА:
  • Функция LEFT извлекает строку слева от текстовой строки.
  • ПРАВО:
  • Функция ПРАВИЛЬНО возвращает текст справа от текстовой строки.
  • СРЕДНЯЯ:
  • Функция MID возвращает определенные символы из середины текстовой строки.
  • ПОИСК :
  • Функция ПОИСК может помочь вам найти положение определенного символа или подстроки в заданном тексте.

Другие статьи:

  • Получить или извлечь первое слово из текстовой строки в Excel
  • Чтобы извлечь все первые слова из списка текстовых строк, разделенных пробелами, вам могут помочь функции ВЛЕВО и НАЙТИ в Excel.
  • Получить или извлечь последнее слово из текстовой строки в Excel
  • Чтобы извлечь последнее слово из текстовой строки, разделенной пробелами, обычно можно создать формулу на основе функций ОБРЕЗАТЬ, ПОДСТАВИТЬ, ПРАВО и ПОВТОР в Excel.
  • Извлечь последнюю строку текста из многострочной ячейки
  • Чтобы извлечь последнюю строку текста из текстовой строки, разделенной разрывами строк, обычно у вас нет прямого способа решить эту проблему. В этой статье я представлю формулу для решения этой задачи в Excel.

Лучшие инструменты для повышения производительности в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите выполнять свою повседневную работу быстро и качественно? Kutools for Excel предоставляет мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.

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

И формула в D5:

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

Левая сторона

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

В этой формуле используется функция НАЙТИ, чтобы найти символ подчеркивания ("_") в тексте, а затем вычитается 1, чтобы вернуться на один символ назад:

НАЙТИ возвращает 11, поэтому результат после вычитания 1 равен 10. Этот результат передается в функцию LEFT в качестве аргумента num_chars, количества символов, которые нужно извлечь из B5, начиная с первого символа. слева:

Функция LEFT возвращает строку "Оценка" в качестве конечного результата.

Правая сторона

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

Как и выше, в этой формуле также используется функция НАЙТИ, чтобы найти символ подчеркивания ("_") в позиции 11. Однако в этом случае мы хотим извлечь текст с правой стороны строки. строка, поэтому нам нужно рассчитать количество символов, которые нужно извлечь из справа. Это делается путем вычитания результата НАЙТИ (11) из общей длины текста в ячейке B5 (21), рассчитанной функцией ДЛСТР:

Результат равен 10, который возвращается функции RIGHT как num_chars, количество символов, которое нужно извлечь из справа:

Конечным результатом в D5 является строка "10 января".

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

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