Як витягти числа із змішаних буквено-цифрових текстових рядків у Excel?

Цей посібник пропонує три методи, які допомагають витягувати числа із змішаного буквено-цифрового текстового рядка в Excel.

Витяг чисел із змішаних буквено-цифрових текстових рядків із формулою
Витяг чисел із змішаних буквено-цифрових текстових рядків із кодом VBA
Легко витягуйте числа із змішаних буквено-цифрових текстових рядків за допомогою дивовижного інструменту

Витяг чисел із змішаних буквено-цифрових текстових рядків із формулою

Ви можете застосувати наведену нижче формулу для вилучення чисел з будь-якого місця текстового рядка в Excel.

1. Виділіть порожню комірку, щоб вивести витягнуті числа, введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім перетягніть ручку автозаповнення вниз, щоб застосувати формулу до інших комірок.

=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 - це клітинка, яка містить текстовий рядок, який потрібно витягти. Будь ласка, змініть його відповідно до ваших потреб.

Потім числа витягуються з будь-якого місця текстового рядка у зазначених клітинках, як показано на скріншоті вище.

Зберігайте числа лише у змішаних буквено-цифрових текстових рядках із кодом VBA

Наступний код VBA може допомогти вам видалити всі нечислові символи із зазначеного діапазону та зберігати в комірках лише цифри. Будь ласка, виконайте наступне.

примітки: Оскільки код перевищує вихідний діапазон безпосередньо, будь ласка, збережіть копію оригінального діапазону на випадок втрати даних.

1. прес інший + F11 клавіші одночасно, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модулі. Потім скопіюйте та вставте наведений нижче код 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 клавіша для запуску коду. Тоді a Kutools для Excel з'явиться діалогове вікно, виберіть діапазон, що містить змішані буквено-цифрові текстові рядки, і ви збережете лише цифри, і натисніть OK кнопки.

Тепер усі нечислові символи у вибраному діапазоні були негайно видалені, і збережені лише цифри. Дивіться знімок екрана:

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

У цьому розділі рекомендовано утиліту Extract Text з Kutools для Excel. За допомогою цієї утиліти можна легко отримати числа з текстового рядка.

1. клацання Кутулс > текст > Витяг тексту щоб увімкнути цю функцію.

2 В Витяг тексту діалоговому вікні, будь ласка, виконайте наведені нижче налаштування.

2.1) Клацніть на Витяг за місцем розташування вкладка (ця вкладка ввімкнена за замовчуванням);

2.2) У Діапазон розділ, натисніть кнопку кнопка, щоб вибрати діапазон комірок, з яких ви будете витягувати текст;

2.3) У Опції розділ: Виберіть Витягніть число варіант;

2.4) Клацніть ОК.

Примітка: Перевірте Вставте як формулу у нижньому лівому куті діалогового вікна вставить результат як формулу в комірки результату. Коли значення вказаної комірки змінюється, результат оновлюється автоматично.

3 В Витяг тексту діалоговому вікні, виберіть порожню комірку для виведення витягнутого тексту, а потім натисніть ОК.

Потім витягуються числа з будь-якої позиції в текстовому рядку. Дивіться знімок екрана: