Excel

Excel формула подстрока: Как разделить текст в Excel на подстроки

ПСТР, ПСТРБ (функции ПСТР, ПСТРБ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функций ПСТР и ПСТРБ в Microsoft Excel.

Описание

Функция ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Функция ПСТРБ возвращает определенное число знаков из текстовой строки, начиная с указанной позиции, на основе заданного числа байтов.

Важно: 

  • Эти функции могут быть доступны не на всех языках.

  • org/ListItem»>

    Функция ПСТР предназначена для языков с однобайтовой кодировкой, а ПСТРБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

  • Функция ПСТР всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

  • Функция ПСТРБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ПСТРБ считает каждый символ за один.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

ПСТР(текст;начальная_позиция;число_знаков)

ПСТРБ(текст;начальная_позиция;число_байтов)

Аргументы функций ПСТР и ПСТРБ описаны ниже.

  • Текст    Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.

  • Начальная_позиция    Обязательный. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

    • Если start_num больше, чем длина текста, то для ПМС и ПБ возвращается «» (пустой текст).

    • Если start_num меньше, чем длина текста, но start_num плюс num_chars превышает длину текста, то mid/MIDB возвращает символы до конца текста.

    • Если start_num меньше 1, то mid/MIDB возвращает #VALUE! значение ошибки #ЗНАЧ!.

  • Число_знаков    Требуется для mid. Указывает, сколько знаков должна вернуть функция ПСТР.

  • Число_байтов    Требуется для midB. Указывает, сколько знаков должна вернуть функция ПСТРБ (в пересчете на байты).

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Поток воды

Формула

Описание

Результат

=ПСТР(A2;1;5)

Возвращает пять знаков из строки в ячейке А2, начиная с первого знака.

Поток

=ПСТР(A2;7;20)

Возвращает двадцать знаков из строки в ячейке А2, начиная с седьмого знака. Так как количество возвращаемых знаков (20) больше длины строки (10), возвращаются все знаки, начиная с седьмого. Пустые символы (пробелы) не добавляются в конец строки.

воды

=ПСТР(A2;20;5)

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

Подстрока из строки в Excel

Автор Амина С. На чтение 10 мин Опубликовано

Работа с текстовыми строками является важной составляющей обработки информации с помощью Excel. С ними удобно работать вручную, если их общее количество относительно небольшое. Но как только число строк становится приличным (например, одна или даже несколько сотен), то операции с ними становятся довольно затруднительными.

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

Содержание

  1. Как в Excel сделать разделение строки на подстроки
  2. Текстовые функции в Эксель
  3. Пример использования текстовых функций в Эксель
  4. Синтаксис функции ПСТР в Excel
  5. Подстрока из строки в Эксель при помощи функции ПСТР

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

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

Также возможно использование макросов для достижения этой цели. Для этого в VBA существует специальная функция – Split. Она разделяет строку по разделителям, в качестве которого может выступать как определенный символ, так и сразу несколько. Синтаксис функции включает три аргумента, из которых обязательным является только один.

  1. Expression. Это строка, которую нужно разбить на подстроки.
  2. Delimiter. Разделитель. Этот аргумент необязательный. Если в нем не указывать никаких значений, то по умолчанию будет в качестве разделителя приниматься пробел.
  3. Limit. То количество подстрок, на которое входная должна быть разделена. Этот аргумент также не обязательно указывать. В этом случае в качестве значения по умолчанию будет установлено -1.
  4. Compare. С помощью этого аргумента функции передается тип сравнения – двоичный или текстовый. Простыми словами, в первом случае (если тип сравнения установлен на 0), функция учитывает регистр букв при сравнении. В случае же текстового сравнения регистр букв не учитывается.

Значение, которое эта функция вернет – массив, в котором перечислены подстроки, число которых задается параметром limit. Как могло заинтересовать наблюдательного читателя, если поставить значение -1, то функцией будут возвращены все подстроки. И теперь давайте приведем несколько примеров, как работает эта VBA функция.

Sub Test1()

Dim a() As String

a = Split(«vremya ne zhdet»)

MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2)

End Sub

Эта функция показывает оповещение, которое выдает три подстроки «vremya ne zhdet». В этом случае стоят настройки по умолчанию. Если записать такой код, то оповещение покажет строку «vremya ne-zhdet» из исходной строки той же самой, только используется вместо пробела дефис.

Sub Test2()

Dim a() As String

a = Split(«vremya-ne-zhdet»,»-«, 2)

MsgBox a(0) & vbNewLine & a(1)

End Sub

Здесь используются значения аргумента Delimiter в –, а Limit – 2. Таким образом, всего возможно была разбивка на три части, но поскольку мы указали только две, то видим, итоговый результат тоже являет собой одну подстроку «vremya» и одну подстроку «ne-zhdet». Видим, что все на самом деле невероятно просто.

Текстовые функции в Эксель

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

  1. БАТТЕКСТ(Значение). Функция, необходимая для превращения ячейки числового формата в текстовый. Ее полезно использовать, если формула требует текстового значения, в то время как в ячейке число представлено в виде цифрового. С помощью данной функции можно конвертировать данные из одного типа в другой.
  2. ДЛСТР(Значение). Эта функция позволяет определить длину строки и то, сколько символов находится в ней. Возвращает число, соответствующее количеству знаков, которые записаны в этой строке.
  3. ЗАМЕНИТЬ(Старый текст, Начальная позиция, число знаков, новый текст). С помощью этой функции можно заменить один текст на другой, в качестве ориентира используя определенное количество знаков, начиная с позиции, которая указана пользователем.
  4. ЗНАЧЕН(Текст). Эта функция совершает противоположную первому оператору операцию – значение текстового формата превращает в числовой.
  5. ЛЕВСИМВ(Строка, Количество знаков). С помощью этой функции можно получить заданное пользователем количество символов строки, указанной человеком. При этом в учет берутся те знаки, которые располагаются слева.
  6. ПРАВСИМВ(Строка, Количество знаков). Принцип работы этой функции аналогичный, только с ее помощью можно вернуть определенное количество знаков справа. То есть, узнать, какой будет часть строки, начиная с самого последнего символа.
  7. НАЙТИ(текст для поиска, текст, в котором ищем, начальная позиция). С помощью этой функции можно получить позицию, на которой находится текст, заданный пользователем. Этот оператор можно использовать, только если регистр для нас важен. Если же нет разницы, какие буквы использовать: большие или маленькие, то есть аналогичная функция – ПОИСК. Также следует отметить, что эта функция будет возвращать исключительно первое вхождение, все последующие уже не берутся в учет. Для этого существуют другие функции.
  8. ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция). Это очень интересная функция. В чем-то она схожа на оператор ЗАМЕНИТЬ, но имеет более широкий функционал. Если пользователь не указал последний аргумент, то замена осуществляется всех вхождений в тексте. Таким образом, это позволяет автоматизировать опции Excel «Заменить все».
  9. ПОДСТРОКА(Текст, разделитель, номер). С помощью этой функции можно получить строку, которая была разделена с помощью разделителя.
  10. ПСТР (Текст, Начальная позиция, Количество знаков). Это одна из самых главных функций, которую мы сегодня будем разбирать очень подробно. Она в чем-то имеет схожий принцип на ЛЕВСИМВ, только дает возможность начать поиск подстроки не с самого начала, а с определенной позиции.
  11. СЦЕПИТЬ (Текст1, Текст2…). Это функция, позволяющая объединить несколько строк. Является некой заменой оператору &. Максимальное количество строк, которые можно соединить между собой – 30.

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

Пример использования текстовых функций в Эксель

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

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

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

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

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

Синтаксис функции ПСТР в Excel

Функция ПСТР в Excel используется наиболее часто, чтобы достать часть строки и использовать ее в дальнейших вычислениях или же просто записать в ячейке. Причина популярности этой функции проста – когда есть большой объем информации, который был импортирован с других программ, то нередко приходится доставать часть из нее в ручном режиме. А с помощью этой функции можно хоть немного автоматизировать процесс. Давайте разберем эту функцию более подробно.

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

  1. Текст. Текстовая строка, из которой мы будем получать «обрезанный» вариант. Кроме результата работы функции и ссылки на ячейку, в качестве параметра этого аргумента может также выступать и непосредственно текстовая константа. Но на практике ее использование лучше всего подходит для тренировки. В реальной жизни в этом нет необходимости, поскольку всегда можно вручную вставить нужный фрагмент текста в любую ячейку.
  2. Начальная позиция. Отсчет знаков для этого аргумента начинается с самого первого символа строки слева. Эта функция отличается от некоторых других тем, что отсчет символов осуществляется с числа 1, а не нуля.
  3. Число знаков. Здесь записывается итоговое количество знаков, которые нам надо отсчитать с начальной позиции. Минимальное значение – 1. Чисто гипотетически есть возможность указать в качестве значения этого аргумента 0, но в этом случае в качестве результата будет получена пустая строка.

В своем самом общем виде формула выглядит так: =ПСТР(текст; начальная_позиция; число_знаков)

Есть еще один вариант этой формулы: ПСТРБ, которая позволяет работать с мультибайтовыми строками. Но в нашем языке таких нет, поэтому достаточно просто знать о том, что такая формула есть. Возможных результата после работы этой формулы два:

  1. Ошибка. Если аргументы функции были неправильно указаны, то появляется ошибка #ЗНАЧ!. Типичные причины появления этой ошибки – нулевая позиция начала или отрицательное значение в аргументе «Число знаков».
  2. Строка. Если все параметры были указаны правильно, мы получаем итоговую текстовую строку.

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

  1. Параметр «Начальная позиция» не может быть больше, чем общая величина строки. Иначе в качестве результата функции будет выдана строка с нулевой длиной (то есть, пустая).
  2. Если, даже несмотря на то, что значение «начальная позиция» меньше итоговой длины строки, сумма значений «начальная позиция» и «число знаков» больше, чем общее количество знаков этого текста, то функцией возвращаются оставшиеся знаки, начиная с той позиции, которая указана. Таким образом, можно указать в качестве числа знаков заведомо большое число, чтобы функция вернула те символы, которые расположены справа до самого конца строки.
  3. Ошибка #ЗНАЧ! возникает в следующих ситуациях: если начальная позиция меньше единицы, число знаков или число байтов (для функции ПСТРБ) отрицательное.

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

Подстрока из строки в Эксель при помощи функции ПСТР

Давайте рассмотрим небольшой пример того, как можно выделить отдельные знаки из строки с помощью функции ПСТР.  Приведем очень простую ситуацию. Предположим у нас в ячейке B14 записана простая строка, состоящая из последовательности чисел от 1 до 0. Предположим, нам надо из строки 1234567890 получить тройку. В таком случае формула должна быть такой: =ПСТР(B14;3;1).

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

Оцените качество статьи. Нам важно ваше мнение:

Подстрока в Excel (Простое руководство)

Середина | Слева | Право | Подстрока в скобках | Подстрока, содержащая определенный текст | Flash Fill

В Excel нет функции SUBSTRING . Используйте MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM и MAX в Excel для извлечения подстрок.

Mid

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

Объяснение: функция MID начинается с позиции 7 (O) и извлекает 6 символов.

Слева

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

Чтобы извлечь подстроку (любой длины) перед дефисом, добавьте функцию НАЙТИ.

Объяснение: функция НАЙТИ находит положение тире. Вычтите 1 из этого результата, чтобы получить правильное количество крайних левых символов. Формула, показанная выше, сводится к ЛЕВОЙ (A1,4-1).

Право

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

Чтобы извлечь подстроку (любой длины) после дефиса, добавьте ДЛСТР и НАЙТИ.

Объяснение: функция ДЛСТР возвращает длину строки. Функция НАЙТИ находит положение тире. Вычтите эти значения, чтобы получить правильное количество крайних правых символов. Показанная выше формула сводится к RIGHT(A1,6-4).

Подстрока между круглыми скобками

Чтобы извлечь подстроку между круглыми скобками (или скобками, квадратными скобками, косой чертой и т. д.), используйте MID и НАЙТИ в Excel.

1. Приведенная ниже формула почти идеальна.

Объяснение: функция НАЙТИ находит положение открывающей скобки. Добавьте 1, чтобы найти начальную позицию подстроки. Показанная выше формула сводится к MID(A1,6+1,2). Эта функция MID всегда извлекает 2 символа.

2. Замените 2 (третий аргумент) формулой, которая возвращает длину подстроки.

Объяснение: вычтите положение открывающей скобки и значение 1 из положения закрывающей скобки, чтобы найти правильную длину подстроки.

Подстрока, содержащая определенный текст

Чтобы извлечь подстроку, содержащую определенный текст (например, символ @), используйте команды ПОДСТАВИТЬ, ПОВТОР, СРЕДНЕЕ, НАЙТИ, ОБРЕЗАТЬ и МАКС в Excel.

1. Сначала используйте ПОДСТАВИТЬ и ПОВТОР, чтобы заменить один пробел 100 пробелами (или любым другим большим числом).

2. Для функции MID ниже начинается 50 (1/2 * большое число) позиций перед позицией символа @ и извлекается 100 (большое число) символов.

3. Используйте функцию TRIM для удаления начальных и конечных пробелов.

4. Соберите все вместе.

Примечание: на шаге 2 функция MID начинается за 50 позиций до позиции символа @. Если адрес электронной почты является первым словом в предложении (ячейка A3), это приводит к отрицательной начальной позиции. В этом случае функция MAX (см. формулу выше) возвращает 1.

Мгновенное заполнение

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

Примечание. Excel не вставляет формулы. Если вы измените текстовые строки в столбце A, Excel не обновит числа в столбце B.

Функции подстроки Excel для извлечения текста из ячейки

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

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

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

  • Извлечь подстроку указанной длины
    • Извлечь текст слева от строки
    • Получить текст справа от строки
    • Извлечь текст из середины строки
  • Извлечь подстроку до или после заданного символа
    • Получить текст перед определенным символом
    • Извлечь текст после определенного символа
    • Вставить текст между двумя вхождениями символа
  • Как найти подстроку в Excel (частичное совпадение)
  • Извлечение текста из ячейки с помощью Ultimate Suite для Excel
  • Дополнительные формулы Excel для подстроки

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

  • ЛЕВАЯ функция — для извлечения подстроки слева.
  • Функция ПРАВО — извлечение текста справа.
  • Функция MID — для извлечения подстроки из середины текстовой строки, начиная с указанной вами точки.

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

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

LEFT(text, [num_chars])

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

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

=ЛЕВО(A2,4)

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

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

ПРАВО(текст , [num_chars])

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

=ПРАВО(A2,4)

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

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

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

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

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

=СРЕДНИЙ(A2,6,3)

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

=ЗНАЧ(СРЕДН(A2,6,3))

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

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

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

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

LEFT( ячейка , SEARCH(» char «, ячейка )-1)

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

=ЛЕВОЕ(A2, ПОИСК("-",A2)-1)

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

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

ПРАВО( ячейка ,ДЛСТР( ячейка )-ПОИСК(» символ «, ячейка ))

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

=ПРАВО(A2,ДЛСТР(A2)-ПОИСК("-",A2))

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

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

MID( ячейка , ПОИСК (« символ «, ячейка )+1, ПОИСК (« символ «, ячейка , ПОИСК (« символ» , ячейка )+1) — ПОИСК («90 симв. 9093») «, ячейка )-1)

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

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

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

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

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

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

=СРЕД(A2, ПОИСК("-",A2) + 1, ПОИСК("-",A2,ПОИСК("-",A2)+1) - ПОИСК("-",A2) - 1)

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

Если вы хотите извлечь текст между 2 nd и 3 rd или 3 nd и 4 th вхождениями одного и того же символа, вы можете использовать один и тот же символ. более компактная комбинация SEARCH SUBSTITUTE для , чтобы получить позицию символа , как описано в разделе Как найти N-е вхождение символа в строку:

НАЙТИ(CHAR(1),SUBSTITUTE( ячейка , символ ,CHAR(1), N-е вхождение ))

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

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

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

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

ISNUMBER(ПОИСК(« подстрока «, ячейка ))

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

=ЧИСЛО(ПОИСК("1zz", A2))

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

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

=ЕСЛИ(ЧИСЛО(ПОИСК("1zz", A2)), "Да", "")

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

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

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

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

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

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

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

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

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

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

  • Чтобы извлечь имя пользователя:

    =ЕСЛИОШИБКА(СЛЕВА(A2,ПОИСК("@",A2)-1),"")

  • Чтобы извлечь домен:

    =ЕСЛИОШИБКА(ПРАВО(A2, ДЛСТР(A2)- ПОИСК("@",A2) - ДЛСТР("@") + 1),"")

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

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

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

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

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

  • Как извлечь число из строки в Excel — примеры формул для получения числа из различных текстовых строк.
  • Как извлечь N-е слово из текстовой строки — как использовать MID в сочетании с 4 другими функциями, чтобы получить любое N-е слово из ячейки.
  • Как извлечь слово, содержащее определенную подстроку — еще одна нетривиальная формула MID, которая извлекает слово, содержащее определенный символ (символы), из любой точки исходной строки.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *