Учебник по Excel — извлечение текста или числа по определенной позиции

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

---

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

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

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

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

1.1 Извлеките первые или последние N символов с помощью формул

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

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

Функция ВЛЕВО может помочь легко извлечь первые N символов из текстовой строки в Excel.

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

=LEFT(text_string,[num_chars])

аргументы

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

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

=LEFT(B5,2)

Теперь первые 2 символа в каждой ячейке диапазона B5:B10 извлечены.

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

Здесь мы применяем функцию ПРАВИЛЬНО для извлечения последних N символов из текстовой строки в Excel.

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

=RIGHT(text_string,[num_chars])

аргументы

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

=RIGHT(B5,2)

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

Хотя приведенные выше формулы просты, для извлечения первых или последних n символов из длинного списка текстовой строки вам все равно нужно перетаскивать дескриптор автозаполнения сверху вниз, что может занять немного времени. Здесь рекомендует Kutools for ExcelАвтора Извлечь текст утилита, помогающая извлечь первые или последние N символов из списка текстовых строк оптом.

1. Заранее выберите список текстовых строк, из которого вы хотите извлечь текст, и щелкните Кутулс > Текст > Извлечь текст.

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

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

3. В следующем всплывающем окне Извлечь текст диалоговом окне выберите ячейку для вывода извлеченных символов, а затем щелкните OK.

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

Нажмите, чтобы узнать больше об этой функции.

---

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

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

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

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

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

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

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

=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 Извлекайте текст до или после первого разделителя с помощью замечательного инструмента

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

1. Выберите диапазон ячеек, из которых вы хотите извлечь текст, а затем щелкните Кутулс > Текст > Извлечь текст.

2. в Извлечь текст диалоговое окно необходимо настроить следующим образом.

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

3. Затем еще один Извлечь текст появится диалоговое окно, выберите ячейку для вывода результатов и нажмите ОК.

Затем тексты до или после первого разделителя сразу извлекаются из выделенных ячеек.

Чтобы узнать больше об этой функции, посетите: Быстрое извлечение определенного текста из ячеек в 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-е слово, давайте применим эту формулу для извлечения 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. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте приведенный ниже 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. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

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

=FindWord(B5,D5)

Or

=FindWord(B5,2)

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

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

2.4.3 Извлеките n-е слово с помощью замечательного инструмента

Если вы не хотите вручную применять формулу или пользовательскую функцию, указанную выше, здесь рекомендуется Kutools for Excel Извлечь n-е слово в ячейку полезность. С помощью этой функции вы можете легко извлечь n-е слово из текстовой строки в ячейке всего за несколько кликов.

1. Выберите ячейку для размещения результата и нажмите Кутулс > Формула Помощник > Текст > Выписка энное слово в ячейке. Смотрите скриншот:

2. в Помощник по формулам диалоговое окно необходимо настроить следующим образом.

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

Нажмите, чтобы узнать больше об этой функции.

2.5 Извлечение текста до или после n-го вхождения разделителя

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

2.5.1 Извлечение текста перед n-м вхождением разделителя

Вы можете использовать функцию ВЛЕВО вместе с функциями ПОДСТАВИТЬ и НАЙТИ для извлечения текста перед n-м вхождением разделителя из ячейки в 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 Извлечение текста после n-го вхождения разделителя

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

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

=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(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 for Excel. Эта функция помогает автоматизировать задачу извлечения в Excel всего за несколько кликов.

1. Нажмите Кутулс > Текст > Извлечь текст для включения этой функции.

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

Ноты: Если вы хотите создать динамические результаты, установите флажок Вставить как формулу коробка. Затем результаты будут автоматически обновляться при изменении данных в диапазоне.

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 for Excel чтобы помочь вам легко извлечь текст между двумя одинаковыми или разными символами в ячейке Excel.

1. Выберите пустую ячейку для вывода результата, а затем щелкните Кутулс > Формула Помощник > Формула Помощник.

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

3. Затем извлекается только текст между «<» и «>» в ​​ячейке B5. Между тем, формула была создана, вы можете выбрать эту ячейку результата, а затем перетащить ее дескриптор автозаполнения вниз, чтобы извлечь тексты из других ячеек в том же списке.

Нажмите, чтобы узнать больше об этой функции.

3.1.4 Извлечение текста между двумя символами (включая символы) по правилу

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

1. Нажмите Кутулс > Текст > Извлечь текст.

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

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 представляет длину символа слова «KTE» плюс 1.

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

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

Многим пользователям Excel может быть трудно запомнить формулы и работать с ними. Здесь, с Извлечь строки между указанным текстом особенность Kutools for Excel, вы можете легко извлечь текст между двумя словами всего за несколько кликов.

1. Выберите ячейку для вывода результата и нажмите Кутулс > Формула Помощник > Формула Помощник.

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 or 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 or 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 для автоматизации задачи в Excel. Вы можете сделать следующее.

1. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. В дебюте Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль. Затем скопируйте приведенный ниже 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 ключ для запуска кода. В открытии KutoolsforExcel диалоговом окне выберите диапазон ячеек, из которых вы хотите извлечь все числа из каждой ячейки, а затем нажмите кнопку OK .

4. Затем еще один KutoolsforExcel всплывает диалоговое окно. В этом диалоговом окне выберите ячейку назначения и щелкните ОК.

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

---

4. Извлечение чисел после определенного текста

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

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. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте приведенный ниже код 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. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

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

=GetNumberAfterTheChar(B5,"No. ")

Ноты:

---

Статьи по теме: