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

While using Excel, we often need to strip our text Cells and need part of our data, like the need of having 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.


Download Practice Workbook

Download this practice workbook below.


6 Ways to Extract Text After Last Space in Excel

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 Right Function to Extract Text After 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 to make 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 delimiter other than space, you just have to replace the space with you desired delimiter.

Read More: How to Extract Text from a Cell in Excel (5 ways)


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 First Space in Excel (3 Methods)Β 


Similar Readings


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

  • At 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 CellΒ C5:Cell C11.

Read More: How to Extract Text between Two Spaces in Excel (5 Methods)


4. Extract Text After Last Space Utilizing XLookup Function

The use of XLOOKUP is to lookup 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

  • At 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 C5: Cell C11.

Read More: How to Extract Text after Second Space in Excel (6 Methods)


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

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, That 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.


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.


Conclusion

To sum it up, the question β€œ in excel, how to extract text after last space” is answered here in 5 different ways. Starting from using the string formulas continued to using XLOOKUP, and FILTERXML formulas ended with using VBA Macros. Among all the methods used here, using string formulas is the easier to understand and simple 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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo