UDF to Extract only Number

 

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

 

img1

 

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

 

img2

 

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)

    Next

NumericOnly = myOutput * 1

End Function

 

img3 

 

  • To get the output in cell B2, the formula is
  • =NumericOnly(A2)

 

img4

 

In this way, we can retrieve numbers only from a cell that contains text & numbers, using VBA code.

 

image 4

Download – UDF to Extract Only Number From A Cell Using VBA – xlsm



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>