How to Extract Multiple Numbers from a String in Excel – 6 Methods

The dataset showcases  ID (7 digits), Name, and ZIP Code (5 digits) in a single cell.

To extract ID and ZIP Code:

Method 1 – Using the TEXTJOIN Function to Extract Multiple Numbers in a Single Cell

Use the TEXTJOIN function.

Step 1: Extracting the Numbers from the String

• Enter the following formula in B5.

B5 is the starting cell of ID, Name & ZIP Code.

Formula Breakdown

• The LEN function returns the number of characters in the B5: 17.
• The INDEX function creates a cell reference based on the found characters.
• The ROW function finds the list of numbers {1,2…17} converted from the cell reference.
• The MID function breaks the characters into arrays.
• The TEXT function along with the “#;-#;0;” argument removes the texts from the cell.
• The TEXTJOIN function joins the numerical values excluding the blank values.

• Press ENTER.
• Drag down the Fill Handle to see the result in the rest of the cells.

Step 2: Extracting the ID and ZIP Code from the Numbers 

C5 is the starting cell of the ID & ZIP Code and 7 is the number of digits of the ID no.

You can also use the RIGHT function.

5 is the number of digits of the ZIP Code.

Method 2 – Applying the TEXTJOIN with the INDIRECT Function 

Combine the INDIRECT function with the TEXTJOIN function.

The formula is:

The IFERROR function is used to skip the #N/A error.

This is the output.

Method 3 – Using the Flash Fill Feature to Extract Multiple Numbers from a String

Use the Flash Fill tool.

• Enter two numbers as shown below.
• While entering the number in the second cell (C6), you’ll see the preview for the cells below.
• Click the last cell of the preview.

This is the output.

Method 4 – Utilizing the Power Query

• Select the entire dataset and go to the Data tab.
• Choose From Table/Range in Get & Transformed Data.

• In the Create Table dialog box, click OK.

• Go to Add Column and click Column From Examples.

• Enter two numbers excluding the string in the new column.
• In the preview for the cells below, click OK.

You’ll get the extracted multiple numbers.

• Rename the Header.
• To load the output to your Excel value, choose Close & Load To in the Home tab.

• Select a location.

This is the output.

Method 5 – Using a VBA Code to Extract Multiple Numbers from a String

Use a VBA code.

• Open a module by clicking Developer > Visual Basic.

• Go to Insert > Module.

• Enter the following code into the module.

Function ExtractMultipleNumbers(Value As String)
Dim LenStr As Integer
LenStr = Len(Value)
Dim i As Integer
Dim CharNum As String

For i = 1 To LenStr
    If IsNumeric(Mid(Value, i, 1)) Then CharNum = CharNum & Mid(Value, i, 1)
Next i
ExtractMultipleNumbers = CharNum
End Function

In the above code, the length of the string (LenStr) and numeric characters (CharNum) are declared as Integer and String. The For…Next statement is used to extract the numbers from the string. A function: ExtractMultipleNumbers is created.

• Go to the Excel sheet and enter Extr to find the function.
• Choose the function with the argument.

This is the output.

Method 6 – Extract Multiple Numbers from a String in Multiple Cells

ID and ZIP Code are given as shown below.

To extract the number in multiple cells:

• To extract the ID, use the following formula.

Formula Breakdown

• The FIND function returns the position of a defined character inside the string. For example, the FIND(“ID:”,B5) returns 1. The LEN function finds the number of characters of the “ID:”is 3.
• The FIND(“ID:”,B5)+LEN(“ID:”),FIND(” “,B5,FIND(“ID:”,B5)+LEN(“ID:”) syntax is used as the start_num argument of the MID function and FIND(“ID:”,B5)-LEN(“ID:”)  is used as the num_chars argument.

This is the output.

• To extract the ZIP Code, use the following formula.

Download Practice Workbook 

<< Go Back to Separate Numbers Text | Split | Learn Excel