Руководство по Excel – Извлечь текст или число по определённой позиции

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

Извлечение Извлечь текст по позиции

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

1. Извлечение — извлеките заданное количество символов слева или справа

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

1,1 Извлечение первых или последних N символов с помощью формул

Предположим, у вас есть список текстовых строк в столбце B, как показано на рисунке ниже. Чтобы извлечь первые 2 и последние 2 символа из каждой строки, воспользуйтесь следующими формулами.

Извлечение Первые N символов из текстовой строки

Функция ЛЕВСИМВ (LEFT) позволяет легко извлекать первые N символов из текстовой строки в Excel.

Общая формула

=LEFT(text_string,[num_chars])

Аргументы

Теперь вы можете использовать эту формулу, чтобы извлечь первые два символа из ячеек столбца B.

1. Выберите пустую ячейку, скопируйте или введите приведённую ниже формулу и нажмите клавишу «Enter», чтобы получить первый результат. Затем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы применить формулу к остальным ячейкам.

=LEFT(B5,2)

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

Извлечение Последние N символов из текстовой строки

Здесь мы используем функцию ПРАВСИМВ (RIGHT), чтобы извлечь последние N символов из текстовой строки в Excel.

Общая формула

=RIGHT(text_string,[num_chars])

Аргументы

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

=RIGHT(B5,2)

1,2 Извлечение первых или последних N символов с помощью мощного инструмента

Хотя приведённые выше формулы просты, для извлечения первых или последних N символов из длинного списка текстовых строк всё равно приходится протягивать маркер автозаполнения сверху вниз — это может занять немало времени. Поэтому мы рекомендуем воспользоваться утилитой «Извлечь текст» из набора «Kutools для Excel», чтобы массово извлекать первые или последние N символов из списка текстовых строк.

1. Заранее выделите список текстовых строк, из которых нужно извлечь текст, и выберите «Kutools» > «Text» > «Извлечь текст».

2. В появившемся диалоговом окне «Извлечь текст» выполните следующие настройки.

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

3. В появившемся диалоговом окне «Извлечь текст» выберите ячейку для вывода извлечённых символов и нажмите «OK».

Таким образом, из выбранных ячеек будут массово извлечены первые N или последние N символов.

Подробнее об этой функции.

2. Извлечение текста до или после определённого символа/слова

В этом разделе представлены различные сценарии, позволяющие извлекать текст до или после определённого символа или слова в соответствии с вашими потребностями.

2,1 Извлечение Извлечь текст до или после первого разделителя (символа)

Как показано на рисунке ниже, чтобы извлечь текст до или после первого разделителя из каждой ячейки диапазона B4:B10, вы можете воспользоваться одним из приведённых ниже методов.

2,1.1 Извлечение Извлечь текст до первого разделителя с помощью формулы

Применение формулы на основе функций ЛЕВСИМВ (LEFT) и НАЙТИ (FIND) поможет извлечь текст до первого разделителя из ячейки. Выполните следующие шаги.

Общая формула

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Аргументы

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

=LEFT(B5,FIND("-",B5,1)-1)

2,1.2 Извлечение Извлечь текст после первого разделителя с помощью формулы

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

Общая формула

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

Аргументы

=MID(B5,FIND("-",B5)+1,LEN(B5))

2,1.3 Извлечение Извлечь текст до или после первого разделителя с помощью мощного инструмента

Здесь мы настоятельно рекомендуем воспользоваться утилитой «Извлечь текст» из набора «Kutools для Excel» — с её помощью вы легко извлечёте текст до или после первого разделителя сразу из всего диапазона ячеек.

1. Выделите диапазон ячеек, из которых нужно извлечь текст, и выберите «Kutools» > «Text» > «Извлечь текст».

2. В диалоговом окне «Извлечь текст» задайте следующие параметры.

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

3. Затем откроется ещё одно диалоговое окно «Извлечь текст» — выберите ячейку для вывода результатов и нажмите «OK».

Таким образом, текст до и после первого разделителя будет одновременно извлечён из выбранных ячеек.

Подробнее об этой функции: Быстрое извлечение определённого текста из ячеек в Excel.

2,2 Извлечение Извлечь текст до или после последнего разделителя (символа)

В предыдущих шагах мы рассмотрели методы извлечения текста до или после первого разделителя в ячейке. Как показано на рисунке ниже, в этом разделе представлены две формулы для извлечения текста до или после последнего разделителя в ячейке. Следуйте приведённым ниже инструкциям.

2,2.1 Извлечение Извлечь текст до последнего разделителя с помощью формулы

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

Общая формула

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

Аргументы

Выберите ячейку, введите приведённую ниже формулу и нажмите клавишу «Enter», чтобы получить результат. Затем выделите ячейку с результатом и протяните её маркер автозаполнения вниз, чтобы извлечь текст из других строк этого же столбца.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2,2.2 Извлечение Извлечь текст после последнего разделителя с помощью формулы

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

Общая формула

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Аргументы

Выберите ячейку, введите приведённую ниже формулу и нажмите клавишу «Enter», чтобы получить результат. Затем выделите ячейку с результатом и протяните маркер автозаполнения вниз, чтобы извлечь текст из остальных строк этого же столбца.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2,3 Извлечение Извлечь текст после n-го символа

Рассмотрим приведённый ниже пример: в диапазоне B4:B10 содержится список текстовых строк. Чтобы извлечь текст, начинающийся с четвёртого символа каждой ячейки, используйте формулу на основе функций ПСТР (MID) и ДЛСТР (LEN).

Общая формула

=MID(text_string,nth_char+1,LEN(text_string))

Аргументы

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

=MID(B5,3+1,LEN(B5))

2,4 Извлечение n-го слова из текстовой строки

Представьте, что у вас есть список текстовых строк, как на снимке экрана ниже, и вы хотите извлечь только N-е слово из каждой строки. В этом разделе описаны три метода, которые помогут вам легко справиться с этой задачей.

2,4.1 Извлечение N-го слова с помощью формулы

Объедините функции TRIM, MID, SUBSTITUTE, REPT и LEN, чтобы извлечь N-е слово из текстовой строки в ячейке.

Общая формула

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Аргументы

В данном случае диапазон B5:B10 содержит текстовые строки, а D5:D10 — числа, указывающие номер извлекаемого слова. Примените эту формулу, чтобы извлечь N-е слово из текстовой строки.

Выберите пустую ячейку, скопируйте в неё приведённую ниже формулу или введите её вручную, а затем нажмите клавишу «Enter», чтобы получить первый результат. Выделите ячейку с этим результатом и перетащите маркер автозаполнения вниз, чтобы извлечь N-е слова из других ячеек.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Примечание: вы можете напрямую указать номер N в формуле, как показано ниже.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2,4.2 Извлечение N-го слова с помощью пользовательской функции

Помимо приведённой выше формулы, вы можете воспользоваться пользовательской функцией для извлечения N-го слова из ячейки в Excel.

1. Нажмите «Alt» + «F11», чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне «Microsoft Visual Basic for Applications» выберите «Вставка» >, «Модуль», затем скопируйте приведённый ниже код VBA в окно редактора кода.

Код VBA: извлечение N-го слова из текстовой строки в ячейке

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount <, 1 Or (Position - 1) >, xCount Or Position <, 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. Нажмите «Alt» + «Q», чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Вернитесь на лист с текстовыми строками, из которых нужно извлечь N-е слово. Выберите пустую ячейку, скопируйте в неё приведённую ниже формулу или введите её вручную и нажмите клавишу «Enter», чтобы получить N-е слово.

=FindWord(B5,D5)

Или

=FindWord(B5,2)

Примечание: в формуле D5 — это ячейка, содержащая номер извлекаемого слова. Вместо ссылки на ячейку вы можете сразу указать нужное число.

5. Выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы извлечь N-е слова из текстовых строк в других ячейках.

2,4.3 Извлечение N-го слова с помощью удобного инструмента

Если вам не хочется вручную применять описанную выше формулу или пользовательскую функцию, воспользуйтесь утилитой «Извлечь N-ое слово из ячейки» из набора Kutools для Excel — с её помощью вы легко извлечёте N-ое слово из текстовой строки в ячейке всего за несколько кликов.

1. Выберите ячейку для размещения результата и последовательно выберите «Kutools» >, «Помощник формул» >, «Текст» >, «Извлечь N-ое слово из ячейки». См. снимок экрана:

2. В диалоговом окне «Помощник формул» задайте следующие параметры.

3. Таким образом, из текстовой строки в ячейке B5 будет извлечено N-е (второе) слово, и одновременно создана формула. Выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы получить N-е слова из других текстовых строк.

Подробнее об этой функции.

2,5 Извлечь текст до или после Вхождение разделителя

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

2,5.1 Извлечь текст до Вхождение разделителя

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

Общая формула

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Аргументы

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

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Примечание: в формуле B5 — это ячейка, содержащая текстовую строку, из которой вы хотите извлечь текст; « » здесь обозначает пробел, а число 2 — второе вхождение пробела. Вы можете изменить эти параметры в соответствии со своими потребностями.

2,5.2 Извлечь текст после Вхождение разделителя

Чтобы извлечь текст после вхождения разделителя, используйте функцию RIGHT вместе с SUBSTITUTE, LEN и FIND.

Общая формула

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Аргументы

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

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

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2,6 Извлечь текст до или после перевода строки

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

2,6.1 Извлечь текст до первого перевода строки с помощью формулы

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

Общая формула

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Аргументы

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

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Таким образом, из каждой ячейки диапазона B5:B8 будет извлечён текст до первого символа перевода строки, как показано на снимке экрана ниже.

Примечание: в формуле CHAR(10) обозначает символ перевода строки в Windows.

2,6.2 Извлечь текст после последнего перевода строки с помощью формулы

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

Общая формула

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

Аргументы

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

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

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

Примечание: в формуле CHAR(10) обозначает разрыв строки в Windows.

2,7 Извлечь текст до или после слова

В предыдущих разделах мы узнали, как извлекать текст до или после определённого символа или разделителя. А что делать, если нужно извлечь текст до или после целого слова? В этом разделе представлены три метода, которые помогут вам решить эту задачу.

2,7.1 Извлечь текст до определённого слова с помощью формулы

Следующая формула поможет вам извлечь текст до определённого слова внутри ячейки Excel.

Общая формула

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Аргументы

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

В этом примере мы извлекаем весь текст до слова «Excel», поэтому просто вводим это слово в формулу, заключив его в двойные кавычки. Также можно указать ссылку на ячейку, содержащую слово «Excel».

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Примечания:

2,7.2 Извлечь текст после определённого слова с помощью формулы

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

Общая формула

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Аргументы

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

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Таким образом, весь текст после слова «Excel» в каждой ячейке будет извлечён, как показано на снимке экрана ниже.

Примечания:

2,7.3 Извлечь текст до или после определённого слова с помощью удобного инструмента

Если вы считаете, что работа с формулами может доставить серьёзные неудобства, настоятельно рекомендуем воспользоваться функцией «Извлечь текст» из набора «Kutools для Excel» — она автоматизирует извлечение данных в Excel всего за несколько кликов.

1. Нажмите «Kutools» > «Текст» > «Извлечь текст», чтобы активировать эту функцию.

2. В диалоговом окне «Извлечь текст» задайте следующие параметры.

2,1) Убедитесь, что вы находитесь во вкладке «Извлечь по позиции»;

2,2) В поле «Диапазон» нажмите кнопку , чтобы выбрать диапазон ячеек, в которых вы хотите выполнить операцию Извлечь текст;

2,3) В разделе «Параметры»:

Чтобы извлечь весь текст до определённого слова, выберите параметр «до текста» Переключатель и введите это слово в текстовое поле;

Чтобы извлечь весь текст после определённого слова, выберите параметр «после текста» Переключатель и введите это слово в текстовое поле.

2,4) Нажмите кнопку «ОК». См. снимок экрана:

Примечание: чтобы получать динамические результаты, установите флажок «Вставить как формулу» — в этом случае данные будут автоматически обновляться при изменении исходного диапазона.

3. Затем откроется диалоговое окно «Извлечь текст» — выберите ячейку для вывода результата и нажмите «OK».

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

Примечание: эта функция учитывает регистр символов.

Подробнее об этой функции.

3. Извлечение текста между символами или словами

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

3,1 Извлечь текст между двумя символами

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

3,1.1 Извлечь текст между двумя одинаковыми символами с помощью формулы

Как показано на скриншоте ниже, в столбце B содержится список текстовых строк, и вам нужно извлечь числовую часть, расположенную между символами «/», из каждой ячейки диапазона. Следующая формула поможет вам справиться с этой задачей.

Выберите пустую ячейку, скопируйте или введите приведённую ниже формулу и нажмите клавишу «Enter», чтобы получить результат. Затем выделите ячейку с результатом и перетащите маркер автозаполнения вниз — так вы быстро получите результаты для всех остальных ячеек списка.

=SUBSTITUTE(MID(SUBSTITUTE("/" &, B5&,REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Таким образом, из каждой ячейки диапазона извлекается текст, заключённый между двумя одинаковыми символами «/». См. скриншот:

Примечание:

 3,1.2 Извлечь текст между двумя разными символами с помощью формулы

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

Общая формула

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Аргументы

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

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Как видно на скриншоте выше, извлечён только текст, расположенный между указанными символами.

3,1.3 Извлечь текст между двумя символами с помощью замечательного инструмента

Здесь мы настоятельно рекомендуем функцию «Извлечь строку между указанными символами» из Kutools для Excel — она легко извлекает текст между двумя одинаковыми или разными символами внутри ячейки Excel.

1. Выберите пустую ячейку для вывода результата и нажмите «Kutools» > «Помощник формул» > «Помощник формул».

2. В диалоговом окне «Помощник формул» задайте следующие параметры.

3. Таким образом, из ячейки B5 извлекается только текст между символами «». Для этого создайте формулу: выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы извлечь текст из других ячеек того же списка.

Подробнее об этой функции.

3,1.4 Извлечь текст между двумя символами (включая сами символы) по правилу

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

1. Нажмите «Kutools» > «Text» > «Извлечь текст».

2. В диалоговом окне «Извлечь текст» задайте следующие параметры.

2,1) Щёлкните вкладку «Извлечь по правилу»;

2,2) В разделе «Диапазон» нажмите кнопку , чтобы выбрать диапазон ячеек, из которых вы хотите извлечь текст между символами;

2,3) В поле «Текст» введите «""»；

Совет: «""» — это символы, между которыми вы хотите выполнить операцию Извлечь текст, а «*» — это маска, обозначающая любое количество символов. Вы можете ввести условие в соответствии со своими потребностями.

2,4) Нажмите кнопку «Добавить», чтобы добавить условие в поле списка «Содержимое правила»;

2,5) Нажмите кнопку «ОК». См. снимок экрана:

3. Появится ещё одно диалоговое окно «Извлечь текст». Выберите ячейку для вывода результата и нажмите кнопку «OK».

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

3,2 Извлечь текст между двумя словами

Помимо извлечения текста между двумя символами, вам может понадобиться извлекать текст между двумя словами. Например, извлеките все фрагменты текста между словами «KTE» и «feature» из каждой ячейки столбца B, как показано на скриншоте ниже. Воспользуйтесь одним из приведённых ниже методов.

3,2.1 Извлечь текст между двумя словами с помощью формулы

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

Общая формула

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Аргументы

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

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Примечание: в формуле число 3 обозначает длину слова «KTE» в символах, а число 4 — длину этого слова плюс 1.

Вы увидите, что из каждой ячейки столбца B извлечены все текстовые строки, расположенные между указанными двумя словами.

3,2.2 Извлечь текст между двумя словами с помощью замечательного инструмента

Многим пользователям Excel бывает непросто запомнить и использовать формулы. С функцией «Извлечь строку между указанными символами» из Kutools для Excel вы легко извлечёте текст между двумя словами всего за несколько кликов.

1. Выберите ячейку для вывода результата и нажмите «Kutools» > «Помощник формул» > «Помощник формул».

2. В диалоговом окне «Помощник формул» выполните следующую настройку.

3. Таким образом, все текстовые строки между словами «KTE» и «feature» из ячейки B5 извлекаются. При этом создаётся формула: выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы Извлечь текст из других ячеек того же списка.

Извлечение чисел по позиции

Для списка буквенно-цифровых строк возможны три случая:

1. Число находится в начале текста;
2. Число находится в конце текста;
3. Число может располагаться в любом месте текста.

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

1 Извлечение числа из начала строки

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

Общая формула

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&,LEN(cell)+1)), 1) *1), 0) -1)

Аргументы

Примечание:

Выберите пустую ячейку, введите приведённую ниже формулу и нажмите «Ctrl» + «Shift» + «Enter» или просто «Enter», чтобы получить результат. Затем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз — так вы быстро заполните остальные ячейки нужными числами.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&,LEN(B5)+1)), 1) *1), 0) -1)

Примечания:

2 Извлечение числа из конца строки

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

Общая формула

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&,LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&,LEN(cell))), 0)))

Аргументы

Примечание:

Выберите пустую ячейку, введите приведённую ниже формулу и нажмите «Ctrl» + «Shift» + «Enter» или просто «Enter», чтобы получить результат. Затем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз — так вы быстро заполните остальные ячейки нужными числами.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&,LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&,LEN(B5))), 0)))

Примечания:

3. Извлечение всех чисел из любого места текстовой строки

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

3,1 Извлечение всех чисел из любого места строки с помощью формулы

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

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

=SUMPRODUCT(MID(0&,B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&,LEN(B5))), 1)) * ROW(INDIRECT("1:"&,LEN(B5))), 0), ROW(INDIRECT("1:"&,LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&,LEN(B5)))/10)

2. Выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы автоматически заполнить остальные ячейки соответствующими числами.

3,2 Извлечение всех чисел из любого места строки с помощью VBA

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

1. Нажмите клавиши «Alt» + «F11», чтобы открыть окно Microsoft Visual Basic for Applications.

2. В открывшемся окне «Microsoft Visual Basic for Applications» выберите «Insert» > «Module», а затем вставьте приведённый ниже код VBA в появившееся окно модуля.

Код VBA: извлечение всех чисел из любого места текстовой строки

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. Нажмите клавишу «F5», чтобы запустить код. В появившемся диалоговом окне «Kutools for Excel» выберите диапазон ячеек, из которых нужно извлечь все числа, и нажмите кнопку «OK».

4. Затем откроется ещё одно диалоговое окно «Kutools for Excel» — выберите ячейку назначения и нажмите «OK».

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

4. Извлечение чисел, следующих за определённым текстом

Как показано на скриншоте ниже, в этом разделе представлено два метода для извлечения любых чисел, следующих за определённым текстом «No.».

4,1 Извлечение чисел после определённого текста с помощью формулы

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

Универсальная формула:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&,"0123456789",FIND("text"," "&,cell&," "))),{2,3,4,5,6}))

Аргументы

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

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&,"0123456789",FIND("No."," "&,B5&," "))),{2,3,4,5,6}))

Примечания:

4,2 Извлечение чисел после определённого текста с помощью пользовательской функции

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

1. Нажмите сочетание клавиш «Alt» + «F11», чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне «Microsoft Visual Basic for Applications» выберите «Insert» > «Module» и вставьте приведённый ниже код VBA в окно модуля.

Код VBA: извлечение чисел после определённого текста в ячейке

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. Нажмите «Alt» + «Q», чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Выберите ячейку, введите приведённую ниже формулу и нажмите клавишу «Enter». Затем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы применить формулу к остальным ячейкам.

=GetNumberAfterTheChar(B5,"No. ")

Примечания:

Связанные статьи:

Лучшие инструменты повышения продуктивности в Office

🤖	KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
	Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
	Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
	Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
	Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
	Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
	Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое

Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…

Подробнее о Kutools для Excel…Бесплатная загрузка…

---

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

• Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
• Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее о Office Tab…Бесплатная загрузка…

---

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

• Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
• Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
• Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
• 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
• Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек

Подробнее о Kutools for Office…Бесплатная загрузка…