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.

**Read More: How to Extract Text Before Character in Excel**

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

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

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

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

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

- Close the window.
- Go to the
**View**tab >**Macros**>**View Macros(Double Click).**

- Select the macros that you created. The name here is
**extract_text_after_last_space** - Click
**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.

## Related Readings

**How to Extract Text after Second Comma in Excel****How to Extract Text between Two Spaces in Excel****How to Extract Text Between Two Characters in Excel****How to Extract Certain Text from a Cell in Excel VBA****How to Extract Text After a Character in Excel**

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