Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF

Add to Favorites
This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great function for splitting numbers and text from cells and can help you with many of your text manipulation / extraction needs. This is especially helpful when separating product id numbers and names and conducting inventory management with spreadsheets, etc.

You can do a few things with this user defined function, including pulling only numbers or retaining decimal places or negative signs (dashes) if, in the original cell, the numbers contained these signs.

Where to install the macro:  Module

UDF to Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel

Function GETNUMBER(rCell As Range, Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double

Dim iCount As Integer
Dim i As Integer
Dim iLoop As Integer
Dim sText As String
Dim strNeg As String
Dim strDec As String
Dim lNum As String
Dim vVal As Variant
Dim vVal2 As Variant

sText = rCell

If Take_decimal = True And Take_negative = True Then

strNeg = "-"
strDec = "."

ElseIf Take_decimal = True And Take_negative = False Then

strNeg = vbNullString
strDec = "."

ElseIf Take_decimal = False And Take_negative = True Then

strNeg = "-"
strDec = vbNullString

End If

iLoop = Len(sText)

For iCount = iLoop To 1 Step -1

vVal = Mid(sText, iCount, 1)

If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then

i = i + 1

lNum = Mid(sText, iCount, 1) & lNum

If IsNumeric(lNum) Then

If CDbl(lNum) < 0 Then Exit For

Else

lNum = Replace(lNum, Left(lNum, 1), "", , 1)

End If

End If

If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))

Next iCount

GETNUMBER = CDbl(lNum)

End Function










How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Excel Forum