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.

**Table of Contents**hide

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

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

** **

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

** **

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

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

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

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

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

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