Как найти первое или все числа в строке в Excel?

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

Найти и извлечь первое число в текстовой строке с помощью формулы массива

Выберите пустую ячейку, в которую нужно поместить первое число из текстовой строки, введите формулу =MID(A2,MIN(IF((ISNUMBER(MID(A2,ROW(INDIRECT("1:"&,LEN(A2))),1)+0)*ROW(INDIRECT("1:"&,LEN(A2)))),ISNUMBER(MID(A2,ROW(INDIRECT("1:"&,LEN(A2))),1)+0)*ROW(INDIRECT("1:"&,LEN(A2))))),1)+0 (A2 — это ячейка с текстом, в которой будет выполняться поиск первого числа) и нажмите одновременно Ctrl+Shift+Enter. Такой способ ввода формулы массива позволяет обрабатывать каждый символ строки по отдельности.
После подтверждения перетащите маркер заполнения вниз, чтобы применить формулу к дополнительным строкам при необходимости.

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

Найти позицию первого числа в текстовой строке с помощью формул

Выберите пустую ячейку, в которую нужно поместить позицию первой цифры, и введите следующую формулу:=MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))) (здесь A2 — ячейка, которую необходимо проверить). Эта формула находит самое раннее вхождение любой цифры (0–9). При необходимости перетащите формулу вниз, чтобы заполнить соседние ячейки.

Примечание: В качестве альтернативы используйте эту формулу массива: =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&,{0,1,2,3,4,5,6,7,8,9})). Не забудьте подтвердить её нажатием Ctrl+Shift+Enter. При копировании формул всегда обновляйте абсолютные ссылки на ячейки, если структура изменяется. Если строка не содержит цифр, эти формулы вернут ошибку. В таких случаях можно обернуть формулу функцией ЕСЛИОШИБКА, чтобы отображать собственные сообщения.

Если ваша цель — извлечь все цифры из ячейки, просто удалив все нечисловые символы, воспользуйтесь удобным решением от утилиты Kutools для Excel: функцией Удалить определенные символы. Она идеально подходит пользователям, которые часто работают с импортированными данными или нуждаются в быстрой массовой очистке ячеек и предпочитают простой интерфейс сложным формулам.

1. Выделите диапазон, из которого нужно извлечь все числа, и перейдите в меню: Kutools > Текст > Удалить определенные символы. См. скриншот для справки:

2. В появившемся диалоговом окне Удалить определённые символы убедитесь, что установлен только флажок Нецифровые, затем нажмите OK, чтобы продолжить.

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

Перед выполнением этой операции рекомендуется создать резервную копию диапазона на случай необходимости отката изменений. Для текстовых строк, содержащих числа, разделённые буквами (например, «A23B17»), все числа будут объединены в одну последовательность («2317»). Если вы ожидаете иной результат, рассмотрите возможность комбинирования этой функции с другими средствами.

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

1. Выберите пустую ячейку, куда нужно поместить извлечённые числа, затем нажмите: Kutools > Функции > Текст > EXTRACTNUMBERS. См. иллюстрацию ниже:

2. В диалоговом окне «Аргументы функции» назначьте соответствующую текстовую ячейку полю Txt, а в поле N введите TRUE (чтобы получить числовое значение). Затем нажмите OK, чтобы подтвердить и извлечь числа.

Примечания:
(1) Вы можете ввести TRUE, FALSE или оставить поле N пустым. Указание TRUE возвращает числовое значение; FALSE или пустое поле вернут числа в виде текста (это удобно для сохранения ведущих нулей).
(2) Эту функцию также можно использовать прямо на листе в качестве формулы: =EXTRACTNUMBERS(A2,TRUE). Введите её в нужную ячейку для вывода результата.
(3) При копировании формулы в соседние строки убедитесь, что ссылки на ячейки при необходимости скорректированы.

Извлечённые числа из каждой текстовой ячейки будут отображаться, как показано ниже:

Когда встроенные средства и формулы оказываются недостаточными — например, если нужно извлечь только первое число, первую полную последовательность цифр или все отдельные числа из строки, — на помощь приходит код VBA, обеспечивающий гибкость и автоматизацию. Этот метод особенно рекомендуется пользователям, которые регулярно сталкиваются с уникальными задачами по извлечению данных в больших массивах или хотят автоматизировать процесс с помощью многократно используемых макросов. Обратите внимание: для этого необходимо включить макросы, а также всегда создавать резервную копию файла перед запуском кода на объёмах данных.

1. В главном окне Excel нажмите: Разработчик > Visual Basic, чтобы открыть редактор VBA. В окне Microsoft Visual Basic для приложений выберите: Вставка > Модуль. Скопируйте и вставьте следующий код в новый модуль:

Извлечь первые числа: извлекает только первую непрерывную последовательность цифр в каждой строке (например, из «abc12def45» будет получено 12).

Sub ExtractFirstNumber()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim sNum As String
    Dim FirstNum As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        sNum = ""
        FirstNum = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                sNum = sNum & Mid(txt, i, 1)
                
                If i = Len(txt) Or Not (Mid(txt, i + 1, 1) Like "[0-9]") Then
                    FirstNum = sNum
                    Exit For
                End If
            ElseIf sNum <> "" Then
                FirstNum = sNum
                Exit For
            End If
        Next i
        
        cell.Offset(0, 1).Value = FirstNum
    Next cell
End Sub

Извлечь все числа: последовательно извлекает все числа, разделяя отдельные числовые блоки пробелами (например, из «abc12def45» получится «12 45»).

Sub ExtractAllNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim arrNums As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        arrNums = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                arrNums = arrNums & Mid(txt, i, 1)
            Else
                If arrNums <> "" Then
                    arrNums = arrNums & " "
                End If
            End If
        Next i
        
        arrNums = WorksheetFunction.Trim(arrNums)
        cell.Offset(0, 1).Value = arrNums
    Next cell
End Sub

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

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

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

Чтобы использовать Быстрое заполнение для извлечения чисел из строки:

1. В ячейке Пустой столбец, расположенной рядом с вашими данными, вручную введите желаемый результат для первой ячейки. Например, если ячейка A2 содержит «OrderA12B3», введите «123» в ячейку B2, чтобы продемонстрировать результат извлечения.
2. Перейдите в ячейку под вашим примером (например, B3) и начните вводить ожидаемый результат для следующей строки. Excel может показать предварительный просмотр распознанного шаблона. Если он соответствует вашим ожиданиям, просто нажмите Enter. В противном случае продолжайте задавать нужные шаблоны, добавив ещё несколько примеров.
3. Кроме того, после ввода примера в первую ячейку выделите всю нужную область размещения списка и перейдите в меню: Данные > Быстрое заполнение(или нажмите)Ctrl + E). Excel автоматически заполнит извлечённые числа в соответствии с распознанным шаблоном.

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

Если Быстрое заполнение не появляется, убедитесь, что оно включено: перейдите в раздел Файл > Параметры > Дополнительно > Параметры правки и установите флажок Автоматически выполнять Быстрое заполнение.