How to Extract Only Numbers from Excel Cell (8 Different Ways)

Often, information or data came with a mix of text and numbers. In that sense, extracting the numbers from the text gives a clear view and better readability. On the other hand, removing unnecessary text from a string and extracting only numbers might be beneficial for better calculation or data cleaning.

For instance, tracking numbers come up with a mixture of formats. It includes both text and numbers. Observe the tracking number here-  “USA1237543”, it includes both text and numbers, where the text is the country code. Once you extract the number, the country code will be more visible and easier to understand.

In this article, we will learn how to extract only numbers from Excel cell using Flash Fill from the option, a combination of formulas, and Excel VBA.

The dataset overall displays the Order No, Customer Name, Tracking Number, and Delivery Date of a shipment. We can extract the numbers from the Order No and the Tracking Number as well by applying the built-in Flash Fill tool.

Flash Fill is an option in Excel that automatically fill the data after providing one or two example in one column or row.

Steps:

➤ First, select another cell to extract the number
➤ Then, write down the number in the selected cell

➤ After that, go to another cell in the same column and start to write down the number until the flash fill pops up as below.

➤ Click on Tab from the keyboard to extract all the numbers, and the output will be similar to below.

There is another option to apply flash fill, if the option does not pop up automatically.

➤ Write down the extracted number in another cell and press  Ctrl  +  E  to apply the flash fill option.

➤ Also, go to Home > Data Tools > Flash Fill to extract the numbers only.

➤ Finally, the output will be similar to the following.

There is a drawback to using Flash Fill, which is the lack of dynamism. To extract the number dynamically, apply the REGEXEXTRACT function.

Steps:

➤ Select a cell and apply the formula below to extract the numbers dynamically.

➤ If the numbers are changed, the extracted numbers will be changed automatically as below.

In this method, we will apply a bunch of functions such as the LEFT function, the SUM function, the LEN function, and the SUBSTITUTE function to extract the numbers from the given dataset from the leftmost side of the data.

Steps:

➤ Select the cell to enter the formula below to extract the cell from the leftmost side of the data.

➤ Lastly, double tab or drag the cursor from the end of the selected cell to copy the same formula in other cells as well.

Here, we will apply the RIGHT function, the LEN function, the MIN function, and the SEARCH function to extract the numbers from the right side of the given data.

Steps:

➤ First, select the cell and enter the formula below

➤ Drag down or double-click on the cursor to complete the process and get output similar to the following.

In this method, we will use the TEXTJOIN function, the TRUE function, the IFIRROR function, the MID function, the ROW function, and the INDIRECT function to extract data from the string and join them together so that the output returns as a number.

Steps:

➤ Select cell D2 to enter the formula below and complete the process after dragging down the cursor till the end of the table.

This method produces a similar output to the previous method. This method will also extract the digits from the string and return the output as a number. But here we will use different functions such as the CONCAT function, the IFERROR function, the MID function, the SEQUENCE function, and the LEN function.

Steps:

➤ Apply the formula below and drag down the cursor till the end of the table to complete the process.

Some information is a mixture of numbers and text, such as a tracking code or any string. In that case, just to extract only the scattered numbers, we will apply a formula that will get the digits of the string and return the digits as numbers.

Steps:

➤ Select the cell to enter the formula below to extract the numbers only from the string.

➤ Drag down the cursor to apply the same formula in the rest of the cell

Excel VBA is a programming language that automates any Excel task. Here, in this method, we will extract the number from a string using Excel VBA with just a single click.

Steps:

➤ Initially, go to Developer > Visual Basic, and the Excel VBA module window will pop up.

➤ Then go to Insert > Module in the Visual Basic window, and a module will pop up to write down the code.

➤ Write down the code in the window and click on the run button, or create a button in the dataset sheet to complete the process.

Code:

Option Explicit
Sub ExtractTrackingNumbers()
Dim rng As Range
Dim cell As Range
Dim i As Long
Dim s As String
Dim result As String
On Error Resume Next
Set rng = Application.InputBox("Select the range:", _
"Tracking Number Extraction", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cell In rng
If Not IsEmpty(cell.Value) Then
s = cell.Value
result = ""
For i = 1 To Len(s)
If Mid(s, i, 1) Like "[0-9]" Then
result = result & Mid(s, i, 1)
End If
Next i
If result <> "" Then
cell.Offset(0, 1).Value = CLng(result)
Else
cell.Offset(0, 1).Value = ""
End If
End If
Next cell
MsgBox "Extraction complete!", vbInformation
End Sub

➤ Go to Developer > Insert > Button from the toolbox

➤ After clicking on the Button option Assign Macro window will pop up
➤ Select the macro and click on OK to assign a button, which is optional
➤ Now, modify the format of the button according to the preference.

➤ The final output will be similar as below

Frequently Asked Questions

How to separate digits from a cell in Excel?

To separate the digits,

➤Select the cell and go to Data > Text to Column option. Where Text to Column wizard will pop up.
➤Then select Fixed Width > Next and select the place where you want to separate the digits.
➤After that, click Next and select the cell to return the output from the Destination cell option.
➤Finally, click Finish to complete the process.

How to extract only characters from a cell in Excel?

Use the LEFT, RIGHT, and MID functions to extract the characters. Sometimes the TRIM and LEN functions are also used to extract the black space only.

How to extract the digits of a number in Excel?

To extract the digits of a number, combine the FIND, LEN, and RIGHT functions.

How to extract numbers before the decimal in Excel?

To extract numbers before the decimal, apply the TRUNC function. For instance, in cell A2, the information is assigned so the formula will be “=TRUNC(A2)”

Concluding Words

In this article, we learned how to extract only numbers from Excel cell using Flash Fill, formulas, and Excel VBA as well. This option is beneficial when there is a combination of numbers and text, and numbers need to be extracted for better readability and a clear view. Feel free to download the practice file and share your thoughts and suggestions.