Как извлечь числа из текстовых строк, содержащих буквы и цифры, в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек