Как извлечь числа из смешанных буквенно-цифровых текстовых строк в Excel?

Это руководство предлагает три метода для извлечения чисел из смешанных буквенно-цифровых текстовых строк в Excel.

Извлечение чисел из смешанных буквенно-цифровых текстовых строк с помощью формулы
Легко извлекайте числа из смешанных буквенно-цифровых текстовых строк с помощью удивительного инструмента
Извлечение чисел из смешанных буквенно-цифровых текстовых строк с помощью кода VBA

Извлечение чисел из смешанных буквенно-цифровых текстовых строк с помощью формулы

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

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

=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),"")

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

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

Легкое извлечение чисел из смешанных буквенно-цифровых текстовых строк с помощью Kutools для Excel

В этом разделе рекомендуется использовать утилиту Извлечения Текста из Kutools для Excel. С помощью этой утилиты извлечение чисел из текстовой строки становится простой задачей.

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

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

2.1) Нажмите вкладку Извлечь по позиции (эта вкладка включена по умолчанию);

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

2.3) В секции Опции: выберите опцию Извлечь числа;

2.4) Нажмите OK.

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

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

Затем числа из любой позиции в текстовой строке будут извлечены. Смотрите скриншот:

Оставить только числа в смешанных буквенно-цифровых текстовых строках с помощью кода VBA

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

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

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

2. В окне Microsoft Visual Basic for Applications нажмите Вставка > Модуль. Затем скопируйте и вставьте приведенный ниже код VBA в окно Модуля.

Код VBA: удаление всех нечисловых символов из ячеек

Sub GetNumbers()
'Updated by Extendoffice 20210125
    Dim xRegEx As Object
    Dim xRg As Range
    Dim xCell As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Pease select range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xRegEx = CreateObject("VBScript.RegExp")
    With xRegEx
        .Pattern = "\D+"
        .IgnoreCase = True
        .Global = True
    End With
    xRg.NumberFormat = "@"
    For Each xCell In xRg
        xCell.Value = xRegEx.Replace(xCell.Value, "")
    Next
    Set xRegEx = Nothing
End Sub

3. Нажмите клавишу F5, чтобы запустить код. Затем появится диалоговое окно Kutools для Excel, пожалуйста, выберите диапазон, содержащий смешанные буквенно-цифровые текстовые строки, из которых вы хотите оставить только числа, и нажмите кнопку OK.

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