Как извлечь числа из смешанных буквенно-цифровых текстовых строк в 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. В диалоговом окне Извлечь Текст выполните следующие настройки.
для выбора диапазона ячеек, из которых вы будете извлекать текст;
Примечание: Установка флажка Вставить как формулу в нижнем левом углу диалогового окна вставит результат как формулу в результирующие ячейки. Когда значение ссылочной ячейки изменится, результат обновится автоматически.
3. В диалоговом окне Извлечь Текст выберите пустую ячейку для вывода извлеченного текста, затем нажмите OK.
Затем числа из любой позиции в текстовой строке будут извлечены. Смотрите скриншот:
Если вы хотите воспользоваться бесплатной пробной версией (30-день) этой утилиты, пожалуйста, нажмите, чтобы скачать её, а затем перейдите к выполнению операции согласно вышеуказанным шагам.
Оставить только числа в смешанных буквенно-цифровых текстовых строках с помощью кода 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 Sub3. Нажмите клавишу F5, чтобы запустить код. Затем появится диалоговое окно Kutools для Excel, пожалуйста, выберите диапазон, содержащий смешанные буквенно-цифровые текстовые строки, из которых вы хотите оставить только числа, и нажмите кнопку OK.
Теперь все нечисловые символы в выбранном диапазоне будут немедленно удалены, и останутся только числа. Смотрите скриншот:
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек