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.

**Table of Contents**hide

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

### 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," ","")))))`

** **

- 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**

**Extract Text Before Character in Excel (4 Quick Ways)****How to Extract Text Between Two Characters in Excel (4 Methods)****Extract Text After a Character in Excel (6 Ways)**

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

** **

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

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

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

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

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

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

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

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