How to Extract Text After Last Space in Excel (6 Ways)

In this article, I am going to use the below dataset for demonstrating purposes. In the range of cells B5:B11, long text in the Name & Designation column is shown. Just to the side of this column is another column containing part of the text after the last space is shown.

Method 1- Use the Right Function to Extract Text After the Last Space

Steps

Select cell C5, where you are going to place the text after the last space in cell B5.
Enter the following formula:

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5," ","#",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))

You will see that the texts after the last space are now in the Designation column.

How Does the Formula Actually Work?

1. LEN(B5): It will return the length of the text character in cell B5. It will return 20.

2. SUBSTITUTE(B5,” ”,””): This function will substitute all the space in cell B5 with no string, In other words, nothing.

3. LEN(SUBSTITUTE(B5,” “,””): This function will return the length of the string after substituting the text with no string. It will return 19.

4. LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)): This function will return the number of the space in the cell B5 text. It will return 1.

5. SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))): With this function, the position of the last space in the Cell text in cell B5 will replaced by #.

6. SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,”-\ “,””)))): Search function will determine the position of # in the string returned in step 5, in other words, the exact position of the space will be identified. It will return 17.

7. LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))): We will get to know the number of character after # by this formula.It will return 3.

8. RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))),B5): As we know the position and no of character after the last space already, we can easily trim the right side of Cell text in Cell B5 by using this formula.

9. IFERROR(RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))),B5): IFERROR function helps to omit any error related to any absence of space or related delimiter.

Note:

If you have a delimiter other than space, you have to replace the space with your desired delimiter.

Method 2 – Use the Trim Function to Extract Text

Steps

Select Cell C5 and enter the following formula:

=TRIM(RIGHT(SUBSTITUTE(B5,"*",REPT(" ",LEN(B5))),LEN(B5)))

After entering the formula, you will see that the text after the last space is separated.
Drag the fill handle button to Cell C11 to split the text after the last space of other data in the B5:B11 cells.

How Does the Formula Actually Work?

1. LEN(B5): LEN function returns a number of text characters in Cell B5.

2. REPT(” “,LEN(B5): Here, the REPT function will return the number of space strings according to the length of the parent text in Cell B5, which we got from the LEN(B5) function.

3. SUBSTITUTE(B5,”-“,REPT(” “,LEN(B5))): Then the SUBSTITUTE function will replace space with the string that we received from the REPT function earlier.

4. RIGHT(SUBSTITUTE(B5,”*”,REPT(” “,LEN(B5)))) : After that RIGHT function will take only the right portion of the string.

5. TRIM(RIGHT(SUBSTITUTE(B5,”*”,REPT(” “,LEN(B5))),LEN(B5))): Finally, the TRIM function will curtail the whole output by removing any extra space.

Note:

If you have a delimiter other than space, you just have to replace the space with your desired delimiter.

Method 3 – Use FilterXML to Extract Text After Last Space

Steps

Select cell C5, and enter the following formula:

=FILTERXML("<t><s>"&SUBSTITUTE(B6," ","</s><s>")&"</s></t>","//s[last()]")

The text string from the B5 cell will convert to an XML string by changing to XML tags from delimiter characters.
After entering the formula, you will see that the last part of the B5 cell is now in cell C5.

Drag the fill handle to cell C11.
You will see that the last part of all texts in column Name & Designation is shown in the range of cells C5:C11.

Method 4 – Extract Text After Last Space Utilizing the XLookup Function

Steps

Select Cell C5, and enter the following formula:

=REPLACE(B5,1,XLOOKUP(" ",MID(B5,SEQUENCE(LEN(B5)),1),SEQUENCE(LEN(B5)),,0,-1),"")

After entering the formula, you will see that the last part of the Cell B5 is now in Cell C5.

Drag the fill handle to cell C11.
You will see that the last part of all texts in column Name & Designation is shown in cell range C5:C11.

Method 5 – Use the Flash Fill Handle to Extract Text After Last Space in Excel

Steps

Enter the last part of the Name & designation part in cell C5 manually.

Drag the Fill Handle icon down while pressing the right mouse button.

After dragging it down to cell C11, release the Fill Handle.
Select Flash Fill from the context menu.

You will see that the last part of all texts in column Name & Designation is shown in the range of cells C5:C11.

Similar Readings

Method 6 – Use VBA Macro to Extract Text After Last Space in Excel

Steps

Go to the Developer tab.
Click Visual Basic.

Click Insert > Module.

In the module window, enter the following code.

Sub extract_text_after_last_space()
Dim rng, cell As Range
Set rng = Range("B5:B11")
For Each cell In rng
cell.Offset(0, 1) = Mid(cell, InStrRev(cell, " "))
Next cell
End Sub

Note :

In this code, the range of cells B5:B11 indicates that this code is going to execute into the range of cells B5:B11. If you have your data in other Cell ranges, replace the range of cells with your desired Cell reference.