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.


Download Practice Workbook 


6 Methods to Extract Multiple Numbers from String in Excel

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.

Dataset

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.

=TEXTJOIN(, 1, TEXT(MID(B5, ROW($AB$1:INDEX($B$1:$B$1000, LEN(B5))), 1), "#;-#;0;"))

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.

Extract Multiple Numbers from String Excel Using TEXTJOIN Function

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

Extract Multiple Numbers from String Excel Using TEXTJOIN Function

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.

=LEFT(C5,7)

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

Finding the ID

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

=RIGHT(C5,5)

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

Finding the ZIP Code

Read More: How to Separate Numbers in One Cell in Excel (5 Methods)


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.

=TEXTJOIN("",TRUE,IFERROR(--MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),""))

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

Extract Multiple Numbers from String Excel Using TEXTJOIN Function with INDIRECT Function

So, the output will be as follows.

Extract Multiple Numbers from String Excel Using TEXTJOIN Function with INDIRECT Function

Read More: How to Separate Numbers in Excel Using Formula (5 Ways)


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.

Extract Multiple Numbers from String Excel Using Flash Fill Feature

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

Extract Multiple Numbers from String Excel Using Flash Fill Feature

Read More: How to Separate Text and Numbers in Excel (4 Easy Ways)


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.

Employig Power Query

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

Employig Power Query

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

Employig Power Query

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

Employig Power Query

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

Employig Power Query

➜ So, fix the location.

Employig Power Query

Finally, you’ll get the following output.

Employig Power Query


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.

How to Insert VBA Code

➜ Secondly, go to Insert > Module.

How to Insert VBA Code

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

Extract Multiple Numbers from String Excel Using VBA Code

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.

Using VBA Code

Eventually, you’ll get the following output.

Extract Multiple Numbers from String Excel Using VBA Code

Read More: How to Separate Numbers From Text in Excel VBA (3 Methods)


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.

Extract Multiple Numbers from String in Multiple Cells

To extract the ID, use the following formula.

=MID(B5,FIND("ID:",B5)+LEN("ID:"),FIND(" ",B5,FIND("ID:",B5)+LEN("ID:"))-FIND("ID:",B5)-LEN("ID:"))

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.

Extract Multiple Numbers from String in Multiple Cells

So, the extracted ID will be as follows.

Extract Multiple Numbers from String in Multiple Cells

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

=MID(B5,FIND("ZIP:",B5)+LEN("ZIP:"),9999)

Extract Multiple Numbers from String in Multiple Cells

Read More: Extract Specific Numbers from a Cell in Excel (11 Ways)


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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

2 Comments
  1. Hi,

    I need your help with excel micro. Could you please take contact with me. I am willing to pay you for the work

Leave a reply

ExcelDemy
Logo