UDF to Extract only Number from a Cell Using VBA in Microsoft Excel
In this article, we will learn how to use VBA code to extract number only from a cell with the user defined function.
Question): I need a macro to retrieve number only from a cell that contains alpha numeric data.
Following is the snapshot of the data in which column A contains alphanumeric data & expected numbers are in column B
In order to get only the numbers from column A; we need to follow the below steps to launch VB editor
- Click on Developer tab
- From Code group, select Visual Basic
Copy the below code in the Standard module
Function NumericOnly(mystr As Variant)
Dim myOutput As String, i As Integer
For i = 1 To Len(mystr)
If IsNumeric(Mid(mystr, i, 1)) Then _
myOutput = myOutput & Mid(mystr, i, 1)
NumericOnly = myOutput * 1
- To get the output in cell B2, the formula is
In this way, we can retrieve numbers only from a cell that contains text & numbers, using VBA code.