How to Pull Numbers from Text in Excel – Extract Numbers from Text Strings

Do you know how to pull numbers from text in Excel when there is no built-in feature for this function?

Not only this, but you can’t even extract the text part from an alphanumeric string. So, what is the solution? Definitely, Excel has always come up with solutions and for this, you will get a better fix. Let’s dive in to see how you can do it:

How to Pull Numbers from Text in Excel?

In this method, you will get to know how you can pull numbers from the end of a text string easily. In an alphanumeric string, the following formula can be used:

RIGHT(cell, LEN(cell) – MAX(IF(ISNUMBER(MID(cell,

ROW(INDIRECT(“1:”&LEN(cell))), 1) *1)=FALSE,

ROW(INDIRECT(“1:”&LEN(cell))), 0)))

With a reference to the cell, these formulas want to replace the original string. In the same row, put the following formula in an empty cell:

=RIGHT(A2, LEN(A2) – MAX(IF(ISNUMBER(MID(A2,

ROW(INDIRECT(“1:”&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT(“1:”&LEN(A2))), 0)))

The above formula provides numbers from the ending string. If your string does not have numbers in the end, you can try another way.

Here you can use the RIGHT function to extract and it is located in the list of TEXT functions. In our example, you will get a numeric value whereas it is not a number. With the VALUE function, the formula needs to be summed up when the final value is a number.

On the other hand, if you need to find errors in a single string, the IFERROR function is helpful:

=IFERROR(VALUE(RIGHT(A2, LEN(A2) – MAX(IF(ISNUMBER(MID(A2,

ROW(INDIRECT(“1:”&LEN(A2))), 1)*1)=FALSE, ROW(INDIRECT(“1:”&LEN(A2))), 0)))), “”)

or

=IFERROR(RIGHT(A2, LEN(A2) – MAX(IF(ISNUMBER(MID(A2,

ROW(INDIRECT(“1:”&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT(“1:”&LEN(A2))), 0))) +0, “”)

Remember that 2019 and earlier versions of Excel use an array formula and for this, you have to press CTRL + SHIFT + ENTER keys from the keyboard.

How to Pull Numbers from Text in Excel with VBA 

VBA as always a functional procedure lets you drag numbers from text in Excel. You can easily modify values to extract numeric data from a string. Below is the VBA code you can use for this query:

Function GetNumeric(CellRef As String)

Dim StringLength As Integer

StringLength = Len(CellRef)

For i = 1 To StringLength

If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)

Next i

GetNumeric = Result

End Function

Let’s understand how this code works by following the steps given here:

• Open the Developer tab.

• Select the Visual Basic option and for this, you can even press ALT + F11 keys.

• In the VB Editor backend, right-click on the objects that appear in the workbook.

• From the Insert menu, choose the Module option.

• Now, copy and paste the VBA code given above in the Module code box.

• Exit from the VB Editor.

• You are all set to use the GetText function in the worksheet. Use the following formula:

=GetNumeric(A2) 

It will instantly give you the number values from the string.

Due to the presence of VBA code in the Workbook, the file will be saved as .xls or .xlsm.

Let’s Wrap Up:

This post clearly explains how to pull numbers from text in Excel. As you know, Excel directly does not have a feature to perform this function that’s why more than one solution is presented here in this post. Continue exploring Excel