How to Extract Numbers from Text String in Excel (Using VBA)


MS ExcelIf you have a text string with numbers and you want extract numbers only, so you can do the same with help of custom formula using excel VBA. In this kind of circumstances general excel formulas won’t work for you but yes excel VBA can help you in this kind of data, I have tried general excel formulas to extract numbers only but failed every time, so I did some research over the internet and find this useful custom formula “SplitText” don’t take formula name seriously you can name it whatever you want, I called it “SplitText”. Below you can find the formula, use it and solve your problem, and you can also use this formula to extract text only out of numbers.


How to Extract Numbers from Text String in Excel (Using VBA)

1st Step: Go to developer tab.

2nd Step: Go to visual basic option.

3rd Step:Go to insert option.

4th Step: Go to module option.

5th Step: Now paste “SplitText” formula in it.

SplitText Formula

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
SplitText = SplitText + xStr
End If
End Function

How to Use SplitText Formula

  • To extract numbers only =SplitText(A1,True)
  • To extract text only =SplitText(A1,False)

Watch This Excel Tutorial on YouTube