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

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

**STEPS:**

- In cell
**C5**insert manually the value of the number part of the string in cell**B5**.

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

- Accept the suggestions and press
**Enter**. - See our desired output in the following image.

**Download Practice Workbook**

You can download the practice workbook from here.

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