How to Extract Numbers after a Specific Text in Excel (2 Suitable Ways)

In this tutorial, we will demonstrate how to extract numbers after a specific text in Excel. In many instances, we need to extract a specific part of a string while working in Microsoft Excel. Excel doesn’t provide any dedicated function to do this. So, we have to use a combination of different Excel functions to perform this action.

How to Extract Numbers after a Specific Text in Excel: 2 Suitable Ways

In this article, we will discuss 3 suitable ways to extract numbers after a specific text in Excel. We can see an image of our dataset in the following screenshot. The dataset contains the names of four football clubs, as well as their founding years. The founding years part in the text string is in number format. So, we will extract that numeric part from the given dataset. For all the examples in this article, we will use the same dataset.

1. Separate Numbers after a Particular Text with Excel Functions

In this part, we will separate numbers after a particular text when numbers can be anywhere in the text. To do this we will also use the combinations of different Excel functions.

1.1 Insert TEXTJOIN, IFERROR, MID, ROW, INDIRECT & LEN Functions

In this method, we will use the combination of TEXTJOIN, IFERROR, MID, ROW, INDIRECT, and LEN functions to extract numbers after a specific text. To do this we will follow some simple steps.

STEPS:

• Firstly, select cell C5.
• Secondly, copy the following formula in that cell:

• Press Enter.
• So, in cell C5 the above command returns the number part from the string of cell B5.
• Thirdly, drag the fill handle tool from cell C5 to C8 to autofill the remaining dataset.
• Lastly, we get the result like the following image.

🔎 How Does the Formula Work?

1.2 Combine LOOKUP, MID, MIN & FIND Functions

In this method, we will use the LOOKUP, MID, MIN, and FIND functions to extract numbers after a specific text in Excel. We will find the numeric part after the text Year from our dataset. Follow the below steps to execute this method.

STEPS:

• To begin with, select cell C5.
• In addition, Type the below formula in that cell:

• Press Enter.
• So, the above action returns the number part in cell C5 from the string of cell B5 after the text Year.
• Furthermore, drag the Fill Handle tool from cell C5 to C8 to autofill the dataset.
• In the end, we get results like the image below.

🔎 How Does the Formula Work?

1.3 Apply MID & SEARCH Functions in Excel

Now, we will use the MID & SEARCH functions to extract numbers after specific text in Excel. Just go through the below steps to perform this method.

STEPS:

• First, select cell C5.
• Next, write down the following formula in that cell:

• Hit Enter.
• So, in cell C5 we can see the value of the number part from the string of cell B5 after the text Year.
• Then, drag the Fill Handle tool from cell C5 to C8.
• Finally, we get the result like the following image.

🔎 How Does the Formula Work?

1.4 Combination of VALUE, TRIM, MID, & FIND Functions

In this method, we will use the VALUE, TRIM, MID, and FIND functions to separate numbers after a particular text in Excel. To do this we will follow the below steps.

STEPS:

• In the beginning, select cell C5.
• Next, insert the following formula in that cell:

• Press Enter.
• Afterward, in cell C5 we get the numbers between the strings Year and Club of cell B5.

• If you want to apply it to cell B6 also insert the following formula in cell C6:

• Hit Enter.
• So, we get the result like the following image.

🔎 How Does the Formula Work?

2. Use VBA Code to Extract Numbers after a Specific Text in Excel

We can also extract numbers after a specific text in Excel with the use of VBA code. In this method, we will create a user-defined function. The user-defined function allows us to create customized functions. To apply VBA code in this method we will go through some simple steps.

STEPS:

• Firstly, go to the Developer tab.
• Secondly, select the option ‘Visual Basic’.

• Thirdly, from the VBA code window select the Insert. From the menu, click on the option Module.

• Next, in the blank VBA code window type the following code:

Function NumbersExtract(CellRef As String)
Dim StrLen As Integer
StrLen = Len(CellRef)
For i = 1 To StrLen
If (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
NumbersExtract = Result
End Function

• Furthermore, click on the save button to save the code.
• So, the above code creates a user-defined function named NumberExtract to extract numbers after a specific text.

• Then select cell C5. Type the user-defined function in cell C5 in the following way:

• Press Enter.
• So, we can see the numbers of cell B5 after the text Year in cell C5.
• After that, to auto-fill the data drag the Fill Handle tool from cell C5 to C8.
• Lastly, we get results like the following image.

Extract Numbers If They Appear at End of Text Every Time in Excel

First and foremost, we will extract a number after a specific text if the numeric portion of the text always appears at the end of the text. To do this we will use a combination of different functions.

1. Combine MIN, FIND & RIGHT Functions to Extract Numbers

In the first method, we will use the MIN, FIND, and RIGHT functions to extract numbers after a specific text in Excel. We will insert an additional column to do this method. Let’s see the steps to perform this method.

STEPS:

• To begin with, insert a new column named Position.

• In addition, select cell C5. Type the following formula in that cell:

• Press Enter. This action returns the starting position of the number in the string in cell C5.
• Furthermore, to autofill, the dataset, drag the Fill Handle tool from cell C5 to C8.

🔎 How Does the Formula Work?

• Next, select cell D5. Insert the following formula in that cell:

• Press Enter.
• As a result, in cell D5 we get the value of the extracted numbers from a string in cell B5.
• After that, drag the Fill Handle tool from cell D5 to D8 to autofill the dataset.
• Lastly, we get the results like the following image.

🔎 How Does the Formula Work?

2. Split Numbers with Excel SUBSTITUTE, LEFT, MIN & FIND Functions

Now, we will use the SUBSTITUTE, LEFT, MIN, and FIND functions to pull out numbers after a particular text in Excel. Let’s see the steps to this method.

STEPS:

• First, select cell C5.
• Next, type the following formula in that cell:

• Then, hit Enter.
• So, in cell C5 the above action returns only the number part from the string of cell B5.
• After that, to autofill, the remaining part, drag the Fill Handle tool from cell C5 to C8.
• Finally, we can see the result in the image below. We get all the number parts from strings of cells (B5:B8) in cells (C5:C8).

🔎 How Does the Formula Work?

3. Use Flash Fill Feature to Extract Numbers If They Appear at End of Text in Excel

One interesting method to extract numbers after a specific text in Excel is to use the Flash Fill feature. Flash Fill feature is only available in Microsoft Excel 365, Microsoft Excel 2013,  and later versions of Microsoft Excel 2013. We can use the method not only when the number part is at the end of the string but also for any positions of the number part in a string. Let’s see how we can do this easily.

STEPS:

• In the beginning, in cell C5 insert manually the value of the number part of the string in cell B5.

• Next, start typing the number part of the next cell.
• If Excel detects a pattern, a preview of data to be auto-filled in the cells below will appear.

• After that, to accept the suggestions press Enter.
• Finally, we can see our desired output in the following image.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In conclusion, this tutorial is a complete guide on how to extract numbers after a specific text in Excel. Use the exercise worksheet that comes with this article to put your skills to the test. If you have any queries, please leave a comment below. Our team will try to respond as quickly as possible. Keep an eye out for more intriguing Microsoft Excel solutions in the future.

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