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

Get FREE Advanced Excel Exercises with Solutions!

While using Excel, we often need to strip our text Cells and need part of our data, like the need to have the last part of the word of the long text. In this article, we are going to discuss how we can extract the last part of the text after space with the help of Excel in 5 different ways that are very simple and convenient.


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 the side of this column is another column containing part of the text after the last space is shown. How we get to this trimmed part of Name & Designation column texts, we will discuss here with explanations with demonstrations.

Extract Text After Last Space. xlsm


1. Using the Right Function to Extract Text After the Last Space

Here, we are going to use Excel formulas such as the RIGHT function, the LEN function, the SEARCH function, the SUBSTITUTE function, and the IFERROR function. Each one of them has a specific role in making this extraction of text after space work altogether.

Steps

  • First, select cell C5, where you are going to place the text after the last space in cell B5.
  • Then, enter the following formula:

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

Using Right Function to Extract Text After Last Space

  • After entering the formula, 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 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 just have to replace the space with your desired delimiter.

Read More: How to Extract Text Before Character in Excel


2. Use of Trim Function to Extract Text

In this method, we are going to use functions like the TRIM function, the RIGHT function, the LEN function, the SEARCH function, the SUBSTITUTE function, and the IFERROR function to extract text from the long string.

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.
  • Then drag the fill handle button to Cell C11. It will split the text after the last space of other data in the B5:B11.

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

Read More: How to Extract Text after a Specific Text in Excel


3. Using FilterXML to Extract Text After Last Space

The Filter-XML function gives out specific data from XML by an XPath expression. XML is a formatted text used primarily for storing and transporting data. They mainly transport data in a specific way.

Steps

  • First, select cell C5, and enter the following formula:
=FILTERXML("<t><s>"&SUBSTITUTE(B6," ","</s><s>")&"</s></t>","//s[last()]")
  • Text string from B5 cell will convert to 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.

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

Read More: How to Extract Text After First Space in Excel


4. Extract Text After Last Space Utilizing the XLookup Function

The use of XLOOKUP is to look for values in the range of arrays. And they return matching values from the return array. XLOOKUP works as a replacement for older functions like HLOOKUP, VLOOKUP, etc.

Steps

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

Extract Text After Last Space Utilizing XLookup

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


5. Use of Flash Fill Handle to Extract Text After Last Space in Excel

The Flash Fill is a very useful tool in Excel. You can easily automate or fill Cells according to your function. It actually recognizes a pattern and fills cells according to those patterns.

Steps

  • First, enter the last part of the Name & designation part in cell C5 manually.

Use of Flash Fill Handle to Extract Text

  • After that, you will see a small box in the corner of cell C5, Which is the Fill Handle icon. Drag it down while pressing the right mouse button.

Use of Flash Fill Handle to Extract Text

  • After dragging it down to cell C11, release the Fill Handle.
  • Then a new context menu will appear, from that context menu, select Flash Fill.

Use of Flash Fill Handle to Extract Text

  • After clicking Flash Fill, 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


6. Using VBA Macro to Extract Text After Last Space in Excel

Using a simple VBA Macro can drastically reduce the time to Extract part of text from a long string.

Steps

  • First, go to the Developer tab, then click Visual Basic.

VBA Macro to Extract Text

  • Then click Insert > Module.

VBA Macro to Extract Text

  • 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, so if you have your data in other Cell ranges, replace the range of cells with your desired Cell reference.

VBA Macro to Extract Text

  • Then close the window.
  • After that, go to the View tab > Macros > View Macros(Double Click).

VBA Macro to Extract Text

  • After clicking View Macros, select the macros that you created just now. The name here is extract_text_after_last_space. Then click Run.

VBA Macro to Extract Text

  • After clicking Run, you will see that the last part of all texts in column Name & Designation is shown in the range of cells C5:C11.


Download Practice Workbook

Download this practice workbook below.


Conclusion

To sum it up, the question in Excel “How to extract text after the last space” is answered here in 6 different ways. Starting from using the string formulas continued to use XLOOKUP, and FILTERXML formulas and ended with using VBA Macros. Among all the methods used here, using string formulas is the easier to understand and simpler one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enabled workbook is attached where you can practice and get used to these methods. Feel free to ask any questions or feedback through the comment section.


Related Readings


<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo