How to Extract Multiple Numbers from String in Excel (6 Methods)

Most likely, you have heard about extracting only numbers. But you might need to extract multiple numbers. That means you may have numeric values before a string and after the string in your dataset. This instructive session demonstrates 6 methods to extract multiple numbers from the string using Excel formulas as well as other useful features with the necessary explanation.

How to Extract Multiple Numbers from String in Excel: 6 Methods

Let’s introduce today’s dataset where the ID (7 digits), Name, and ZIP Code (5 digits) of some employees are provided. Needless to say, this data for each employee is available in a single cell e.g. B5 cell. Now, you need to extract the ID and ZIP Code from the corresponding cell to either a single cell or multiple cells.

If you want to pull out the multiple numbers from the string in a single cell, you can follow the first 5 methods and the rest method is for extracting the numbers in multiple cells.

Let’s explore the methods.

1. Using TEXTJOIN Function to Extract Multiple Numbers in a Single Cell

Firstly, you can use the TEXTJOIN function along with other Excel functions to extract multiple numbers from a cell.

Step 01: Extracting the Numbers from String

So, insert the following formula in the B5 cell.

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

⧬ Formula Explanation:

• The LEN function returns the number of characters in the B5 cell which is 17.
• The INDEX function creates a cell reference based on the found characters.
• The ROW function finds the list of numbers e.g. {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.
• Lastly, the TEXTJOIN function joins the numerical values excluding the blank values of the text.

After pressing ENTER and utilizing the Fill Handle tool, you’ll get the following output.

Step 02: Pulling out ID and ZIP Code from the Numbers 

Now, you can easily, pull out the ID from the output found in the C5 cell.

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

Similarly, you can use the RIGHT function to pull out the ZIP Code.

Here, 5 is the number of digits of the ZIP Code.

2. Applying TEXTJOIN with INDIRECT Function 

Instead of using the INDEX function, you can apply the INDIRECT function with the TEXTJOIN function. So, the formula will be like the following.

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

So, the output will be as follows.

3. Using Flash Fill Feature to Pull out Multiple Numbers from String

One of the simplest ways to extract multiple numbers from a string is to use the Flash Fill tool.

➜ Initially, you have to type two numbers as depicted in the following screenshot.

➜ While you are typing the number in the second cell (e.g. C6 cell), you’ll see the preview for the below cells. Then, just click over the last cell of the preview.

After doing that, you’ll get the following output without any hassle and complexity.

4. Utilizing Power Query

Sometimes, the Flash Fill tool might not work. In that case, you can execute the same task using the Power Query, a data transformation and preparation engine in Excel. Follow the steps below.

➜ Primarily, select the entire dataset and choose From Table/Range option from the Get & Transformed Data tab in the Data tab.

➜ Then, you’ll see the dialog box namely Create Table. Just press OK.

➜ Immediately, you’ll get the following window having the dataset. Now, go to the Add Column tab and click on the Column From Examples option.

➜ Later, type two numbers excluding the string in the new column. Shortly, you’ll see the preview for the below cells. Now, just click over the OK option.

➜ Subsequently, you’ll get the extracted multiple numbers. After renaming the Header, you’ll see the following output.

➜ Now, if you want to load the output in your Excel value, choose Close & Load To from the Home tab.

➜ So, fix the location.

Finally, you’ll get the following output.

5. Using VBA Code to Extract Multiple Numbers from String

Unfortunately, there is no dedicated function in Excel to extract the multiple numbers from the string. What if you get such type of function using the VBA code?

Before doing that, you need to create a module to insert the VBA code.

➜ Firstly, open a module by clicking Developer > Visual Basic.

➜ Secondly, go to Insert > Module.

Then, copy the following code into the newly created 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 types respectively. Then, I used the For…Next statement is to pick out the numbers from the spring. Therefore, a function namely ExtractMultipleNumbers is created!

Now, go to the Excel sheet and type Extr to find the function. Immediately, you’ll see the function. Then, just choose the function with the argument.

Eventually, you’ll get the following output.

6. Extract Multiple Numbers from String in Multiple Cells

Assuming that the ID and ZIP Code are given as shown in the following screenshot. And you want to extract the number in multiple cells. That means you want to extract the ID and ZIP Code in different cells.

To extract the ID, use the following formula.

⧬ Formula Explanation:

• 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.
• However, 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.

So, the extracted ID will be as follows.

To pull out the ZIP Code, use the following formula.

Download Practice Workbook 

Conclusion

That’s all for today’s session. I strongly believe you can easily extract multiple numbers from the stirring in Excel. Anyway, share your thoughts in the comments section below.

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