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

Method 1 – Separate Numbers after a Particular Text with Excel Functions

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

STEPS:

Select cell C5.
Copy the following formula in that cell:

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

Press Enter.
In cell C5 the above command returns the number part from the string of cell B5.
Drag the fill handle tool from cell C5 to C8 to autofill the remaining dataset.
Get the result like the following image.

How Does the Formula Work?

INDIRECT(“1:”&LEN(B5)): The INDIRECT function takes the value of LEN(B5) as cell reference which is 26.
ROW(INDIRECT(“1:”&LEN(B5))): The Row function uses the return value by the INDIRECT function as a reference.
MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1): The MID function extracts the number of parts from cell B5.
IFERROR(MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1)*1,””): If the MID function finds a valid value the IFERROR function returns that otherwise it returns blank.
TEXTJOIN(“”,TRUE,IFERROR(MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1)*1,””)): The TEXTJOIN function returns the number part from the string in cell B5.

1.2 Combine LOOKUP, MID, MIN & FIND Functions

STEPS:

Select cell C5.
Type the below formula in that cell:

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("Year"," "&B5&" "))),{2,3,4,5,6}))

Press Enter.
It returns the number part in cell C5 from the string of cell B5 after the text Year.
Drag the Fill Handle tool from cell C5 to C8 to autofill the dataset.
Get results like the image below.

How Does the Formula Work?

FIND(“Year”,” “&B5&” “): The text value Year in cell B5.
FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″,FIND(“Year”,” “&B5&” “)): This part finds the numeric values after the text Year in cell B5.
MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″,FIND(“Year”,” “&B5&” “))): The MIN function returns the last position of the number characters.
MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″,FIND(“Year”,” “&B5&” “))),{2,3,4,5,6}): The MID function extracts the number characters after the text Year from a string.
LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″,FIND(“Year”,” “&B5&” “))),{2,3,4,5,6})): The specified conditions in cell B5. Then returns the number part after the text Year from the string of cell B5.

1.3 Apply MID & SEARCH Functions in Excel

STEPS:

Select cell C5.
Write down the following formula in that cell:

=MID(B5,SEARCH("Year ",B5)+4,SEARCH(" Club",B5)-SEARCH("Year ",B5)-4)

Hit Enter.
In cell C5 we can see the value of the number part from the string of cell B5 after the text Year.
Drag the Fill Handle tool from cell C5 to C8.
Get the result like the following image.

How Does the Formula Work?

SEARCH(“Year “,B5): The SEARCH function returns the location of string Year inside the sting of cell B5.
SEARCH(” Club”,B5)-SEARCH(“Year “,B5)-4: This part counts the characters between the strings Club and Year.
MID(B5,SEARCH(“Year “,B5)+4,SEARCH(” Club”,B5)-SEARCH(“Year “,B5)-4): The MID function returns the number part between the strings Year and Club.

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

STEPS:

Select cell C5.
Insert the following formula in that cell:

=VALUE(TRIM(MID(B5,18,FIND("Club",B5)-19)))

Press Enter.
In cell C5 we get the numbers between the strings Year and Club of cell B5.

Apply it to cell B6 also insert the following formula in cell C6:

=VALUE(TRIM(MID(B6,16,FIND("Club",B6)-17)))

Hit Enter.
Get the result like the following image.

How Does the Formula Work?

FIND(“Club”,B6): The FIND function finds the position of string Club in cell B6.
MID(B6,16,FIND(“Club”,B6)-17): The MID function selects the values from the 16th character until the string Club.
TRIM(MID(B6,16,FIND(“Club”,B6)-17))): TRIM function extracts the return portion defined by the previous part.
VALUE(TRIM(MID(B6,16,FIND(“Club”,B6)-17))): The VALUE function in this part returns the numeric part of cell B6 in cell C6.

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

STEPS:

Go to the Developer tab.
Select the option ‘Visual Basic’.

From the VBA code window, select the Insert. From the menu, click Module.

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

Click on the save button to save the code.
The above code creates a user-defined function named NumberExtract to extract numbers after a specific text.

Select cell C5. Type the user-defined function in cell C5 in the following way:

=NumbersExtract(B5)

Press Enter.
See the numbers of cell B5 after the text Year in cell C5.
Auto-fill the data and drag the Fill Handle tool from cell C5 to C8.
Get results like the following image.

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

Method 1 – Combine MIN, FIND & RIGHT Functions to Extract Numbers

STEPS:

Insert a new column named Position.

Select cell C5. Type the following formula in that cell:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))

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

How Does the Formula Work?

FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″): The FIND function finds the numeric parts in cell B5.
MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″)): The MIN function returns the first position of numbers in the text string of cell B5.

Select cell D5. Insert the following formula in that cell:

=RIGHT(B5,LEN(B5)-C5+1)

Press Enter.
In cell D5 we get the value of the extracted numbers from a string in cell B5.
Drag the Fill Handle tool from cell D5 to D8 to autofill the dataset.
Get the results like the following image.

How Does the Formula Work?

LEN(B5)-C5+1: The LEN function returns the length of cell B5. Subtract the value of cell C5 and add 1 with the value of LEN(B5).
RIGHT(B5,LEN(B5)-C5+1): The RIGHT function returns the strings after the position returned by the part LEN(B5)-C5+1.

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

STEPS:

Select cell C5.
Type the following formula in that cell:

=SUBSTITUTE(B5,LEFT(B5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5),""))-1),"")

Hit Enter.
In cell C5 the above action returns only the number part from the string of cell B5.
Autofill, the remaining part, drag the Fill Handle tool from cell C5 to C8.
See the result in the image below. We get all the number of parts from strings of cells (B5:B8) in cells (C5:C8).

How Does the Formula Work?

FIND({0,1,2,3,4,5,6,7,8,9},B5): The FIND function in this part finds the numeric parts in cell B5.
IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5),””): If the FIND function returns any error value the IFERROR function returns a blank value.
MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5),””))-1): The MIN function in this part returns the least position of number from the string in cell B5. In our example is 12.
LEFT(B5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5),””))-1): Here, the LEFT function extracts 12 characters from the string in cell B5.
SUBSTITUTE(B5,LEFT(B5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5),””))-1),””): The SUBSTITUTE function substitutes the 12 characters with blank value and returns only the number parts.