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

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

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

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

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

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

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

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

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

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

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

=LEFT(text_string,[num_chars])

Аргументы

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

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

=LEFT(B5,2)

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

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

Здесь мы используем функцию RIGHT для извлечения последних N символов из текстовой строки в Excel.

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

=RIGHT(text_string,[num_chars])

Аргументы

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

=RIGHT(B5,2)

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

Хотя приведённые выше формулы просты, для извлечения первых или последних n символов из длинного списка текстовых строк всё равно нужно протягивать маркер автозаполнения сверху вниз, что может занять время. В этом случае рекомендуем использовать функцию "Извлечь текст" в Kutools для Excel, чтобы массово извлекать первые или последние N символов из списка текстовых строк.

1. Заранее выделите список текстовых строк, из которых нужно извлечь текст, и нажмите "Kutools" > "Текст" > "Извлечь текст".

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

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

3. В следующем появившемся диалоговом окне "Извлечь текст" выберите ячейку для вывода извлечённых символов и нажмите "ОК".

Теперь первые 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" > "Текст" > "Извлечь текст".

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

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

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

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

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

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

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

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

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

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

=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. Нажмите клавиши "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 — это ячейка, содержащая число, обозначающее n-ое слово. Вы также можете напрямую заменить ссылку на ячейку числом.

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

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

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

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

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

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

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

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

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

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

Вы можете использовать функцию LEFT в сочетании с функциями SUBSTITUTE и FIND для извлечения текста до 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, 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. Затем появится диалоговое окно "Извлечь текст", выберите ячейку для вывода результата и нажмите кнопку "ОК".

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

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

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

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" > "Текст" > "Извлечь текст".

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

2.1) Перейдите на вкладку "Извлечь по правилу";

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

2.3) В поле "Текст" введите "<*>"；

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

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

2.5) Нажмите кнопку "ОК". Смотрите скриншот:

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

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

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

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

2. В открывшемся окне "Microsoft Visual Basic for Applications" выберите "Вставка" > "Модуль". Затем скопируйте приведённый ниже код 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 выберите диапазон ячеек, из которых нужно извлечь все числа, затем нажмите кнопку "ОК".

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

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

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" выберите "Вставка" > "Модуль" и скопируйте приведённый ниже код 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 Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
	Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
	Супер ПОИСК: 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... Бесплатная загрузка...