Как извлечь только числа из текстовой строки в Excel?

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

Извлечение числа из любой позиции текстовой строки

• С помощью формул
• С помощью Kutools для Excel
• С помощью кода VBA

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

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

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

Извлечение числа из любой позиции текстовой строки

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

♦ Извлечение числа из любой позиции текстовой строки с помощью формул

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

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

● Все версии Excel:

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

● Excel 365 или Excel 2021 и более поздние версии:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))

♦ Извлечение числа из любой позиции текстовой строки с помощью формул

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

1. Нажмите Kutools > Текст > Извлечь текст, см. скриншот:
   
2. В Извлечь текст диалоговом окне выполните следующие операции:
   (1.) Выберите список данных, из которого нужно извлечь числа;
   (2.) Выберите Извлечь числа опцию;
   (3.) Установите флажок Вставить как формулу опцию, если это необходимо;
   (4.) Наконец, нажмите OK кнопку.
   
3. В следующем диалоговом окне выберите ячейку для вывода результата извлечения, см. скриншот:
   
4. Нажмите OK, все числа из каждой ячейки извлекаются одновременно. См. скриншот:
   

♦ Извлечение числа из любой позиции текстовой строки с помощью кода VBA

Вот код VBA, который также может помочь вам, выполните следующие действия:

1. Удерживайте клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
2. Нажмите Вставить > Модуль, и вставьте следующий код в Модуль Окно.
   Код VBA: Извлечение только чисел из текстовой строки:

   Sub ExtrNumbersFromRange()
       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 клавишу для запуска этого кода, и появится диалоговое окно, напоминающее выбрать текстовый диапазон, который вы хотите использовать, см. скриншот:
   
4. Затем нажмите OK, появится еще одно диалоговое окно, пожалуйста, выберите ячейку для вывода результата, см. скриншот:
   
5. В конце нажмите кнопку OK, и все числа в выбранных ячейках будут извлечены одновременно.

Извлечение только десятичных чисел из текстовой строки

Если текстовые строки в вашей таблице содержат некоторые десятичные числа, как можно извлечь только десятичные числа из текстовых строк?

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

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

● Все версии Excel:

=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))

● Excel 365:

=REGEXEXTRACT(A2,"[\d.]+")

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

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

Скопируйте и вставьте приведенную ниже формулу в пустую ячейку, нажмите Ctrl + Shift + Enter, чтобы получить первый результат:

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

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

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

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

Скопируйте и вставьте приведенную ниже формулу в пустую ячейку, нажмите Ctrl + Shift + Enter, чтобы получить первый результат:

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

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

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

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

• Извлечение только жирного текста в Excel
• Извлечение только текста в Excel