To Extract Number only from String in MS Excel
We will use a VBA function to extract all numbers from text string in Excel. The steps are follows:
Step1: At first open MS Excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or press “ALT+F11” shortcut to open Microsoft Vusual Basic for Application(VBA) code editor.
Step2: click “Insert” ->”Module” to create a new module.
Step3: Nowpaste the below VBA code into the code editor. Then clicking “Save” button.
Function numbersonly(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
numbersonly = .Replace(Txt, "")
End With
End Function
Step4: Now type the following formula in a blank cell, and then press Enter key. You will get the numbers only data and drag the AutoFill Handle over to other cells to get from all cells text.
= numbersonly(B1)
