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.

**Table of Contents**hide

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

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.

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.

`=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.

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

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

So, the output will be as follows.

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

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

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

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

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

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 theargument of the*start_num***MID**function and**FIND(“ID:”,B5)-LEN(“ID:”)**is used as theargument.*num_chars*

So, the extracted *ID *will be as follows.

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

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

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

Hi,

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

Hello, RUBAN! You can send your Excel Macro related problem to this email: [email protected]. We’ll start working on your problem as soon as we receive your mail! Thanks.