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

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.

### 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.

Tutorial Details