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:

Dataset

 


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

.Step 1: Extracting the Numbers from the String

  • Enter the following formula in B5.

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

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.

Extract Multiple Numbers from String Excel Using TEXTJOIN Function

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

Extract Multiple Numbers from String Excel Using TEXTJOIN Function

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

=LEFT(C5,7)

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

Finding the ID

You can also use the RIGHT function.

=RIGHT(C5,5)

5 is the number of digits of the ZIP Code.

Finding the ZIP Code


Method 2 – Applying the TEXTJOIN with the INDIRECT Function 

he formula is:

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

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

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

This is the output.

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


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

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

Extract Multiple Numbers from String Excel Using Flash Fill Feature

This is the output.

Extract Multiple Numbers from String Excel Using Flash Fill Feature


Method 4 – Utilizing the Power Query

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

Employig Power Query

  • In the Create Table dialog box, click OK.

Employig Power Query

  • Go to Add Column and click Column From Examples.

Employig Power Query

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

Employig Power Query

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.

Employig Power Query

  • Select a location.

Employig Power Query

This is the output.

Employig Power Query


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

  • Open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

  • Go to Insert > Module.

How to Insert VBA Code

  • 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

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

Using VBA Code

This is the output.

Extract Multiple Numbers from String Excel Using VBA Code


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:

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

Extract Multiple Numbers from String in Multiple Cells

This is the output.

Extract Multiple Numbers from String in Multiple Cells

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

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

Extract Multiple Numbers from String in Multiple Cells


Download Practice Workbook 


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo