In this article, weâ€™re going to **find** the** last occurrence** of a **character** in a **string** in **Excel**. Our sample dataset has** three columns**: **Company Name**, **Employee Code**, and **Last Occurrence**. **Employee Code **contains the name, age, and the department of an employee.

For the first **4** methods, weâ€™ll find the position of the forward-slash â€ś**/**â€ť in for all the values in **Employee Code**. After that, weâ€™re going to output **strings** after the last slash in the last **2** methods.

**Table of Contents**hide

**Download Practice Workbook**

**6 Ways in Excel to Find Last Occurrence of Character in String**

### 1. Using FIND & SUBSTITUTE Functions in Excel to Find Position of Last Occurrence of Character in String

For the first method, weâ€™re going to use the **FIND** function, the **SUBSTITUTE** function, the **CHAR** function, and the **LEN** function to **find** the **last** position of the slash in our **string**.

**Steps:**

- Firstly, type the following formula in
**cell D5**.

`=FIND(CHAR(134),SUBSTITUTE(C5,"/",CHAR(134),(LEN(C5)-LEN(SUBSTITUTE(C5,"/","")))/LEN("/")))`

**Formula Breakdown**

Our main function is **FIND**. Weâ€™re going to find the **CHAR(134)** value in our string.

**CHAR(134)****Output: â€**.- We need to set a character that is not present in our strings. Weâ€™ve chosen it because it is rare in strings. If somehow you have this in your strings, change it to anything that is not in your strings (for example â€ś
**@**â€ť, â€ś**~**â€ť, etc.).

**SUBSTITUTE(C5,â€ť/â€ť,CHAR(134),(LEN(C5)-LEN(SUBSTITUTE(C5,â€ť/â€ť,â€ťâ€ť)))/LEN(â€ś/â€ť))**-> becomes,**SUBSTITUTE(C5,â€ť/â€ť,â€ťâ€ â€ť,(17-LEN(â€śMike32Marketingâ€ť))/1)**-> becomes,**SUBSTITUTE(â€śMike/32/Marketingâ€ť,â€ť/â€ť,â€ťâ€ â€ť,(17-15)/1)****Output: â€śMike/32â€ Marketingâ€ť**.

- Now our full formula becomes,
**=FIND(â€śâ€ â€ť,â€ťMike/32â€ Marketingâ€ť)****Output: 8**.

- Secondly, press
**ENTER**.

Weâ€™ll see the value **8**. If we count manually from the left side, we will get **8** as the position for the **slash** in **cell C5**.

- Finally, use the
**Fill Handle**to copy the formula down.

Thus, weâ€™ve got the position of the **last occurrence** of a **character** in our** string**.

**Read More:** **Excel Function: FIND vs SEARCH (A Comparative Analysis)**

### 2. Applying MATCH & SEQUENCE Functions in Excel to Find Position of Last Occurrence of Character in String

For the second method, weâ€™re going to use the **MATCH** function, the **SEQUENCE** function, the **MID** function, and the **LEN** function to find the position of the **last occurrence **of a **character **in the **string**. Remember the **SEQUENCE** function is only available on **Excel 365** or **Excel 2021**.

**Steps:**

- Firstly, type the following formula in
**cell D5**.

`=MATCH(2,1/(MID(C5,SEQUENCE(LEN(C5)),1)="/"))`

**SEQUENCE(LEN(C5))****Output: {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}**.- The
**LEN**function is measuring the length of**cell C5**. The**SEQUENCE**function returns a list of numbers sequentially in an array.

**MATCH(2,1/(MID(C5,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)=â€ť/â€ť))****Output: 8**.- The
**Match**function is finding the last**1**value in our formula. It is in the**8thÂ**position.

- Secondly, press
**ENTER**.

Using the formula, weâ€™ve found the position of **forward-slash** as **8** in our **string**.

- Finally, use
**Fill Handle**to**AutoFill**the formula.

In conclusion, weâ€™ve applied another formula to find the **last position** of a **character** in the **strings**.

**Read More:** **How to Find Character in String Excel (8 Easy Ways)**

### 3. Utilizing an Array Formula in Excel to Find Position of Last Occurrence of Character in String

Weâ€™re going to use the **ROW** function, the **INDEX** function, the **MATCH**, the** MID**, and the **LEN** functions to create an array formula to find the position of the **last occurrence** of a **character **in a **string**.

**Steps:**

- Firstly, type the formula from below to
**cell D5**.

`=MATCH(2,1/(MID(C5,ROW($C$1:INDEX(C:C,LEN(C5))),1)="/"))`

**Formula BreakdownÂ **

The formula is similar to method **2**. Weâ€™re using the **ROW** and the **INDEX** function to replicate the output as the **SEQUENCE** function.

**ROW($C$1:INDEX(C:C,LEN(C5)))****Output: {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}**.- We can see the output is the same. The
**INDEX**function returns the value of a range. The**LEN**function is counting the length of the string from**cell C5**. Finally, the**ROW**function is returning the**cell**values from**1**to**cell**length of**C5**. The rest of the formula is the same as method**2**.

- Secondly, press
**ENTER**.

Weâ€™ve got **8** as the value as expected. Our formula worked flawlessly.

**Note:** Weâ€™re using the **Excel 365** version. If youâ€™re using an older version then you will need to press **CTRL** + **SHIFT** + **ENTER**.

- Finally, double-click or drag down the
**Fill Handle**.

This is what the final step should look like.

**Read More:** **Find First Occurrence of a Value in a Range in Excel (3 Ways)**

**Similar Readings**

**How to Find * Character Not as Wildcard in Excel (2 Methods)****How to Use Excel Formula to Find Last Row Number with Data (2 Ways)****Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)****How to Find Links in Excel****Find External Links in Excel (6 Quick Methods)**

### 4. User Defined Function to Find Position of Last Occurrence of Character in String

In this method, we will use a custom **VBA** formula to find the last position of a **character** in a **string**. Without further ado, letâ€™s jump into the action.

- Firstly, press
**ALT + F11**to bring up the**VBA**window.

You can choose **Visual Basic** from the **Developer** tab to do so too.

- Secondly, From
**Insert**>>> select**Module**.

- Thirdly,
**copy**and**paste**the following code.

```
Function LOccurence(x1 As String, x2 As String)
LOccurence = InStrRev(x1, x2)
End Function
```

Weâ€™ve created a custom function called â€ś**LOccurence**â€ť. The **InStrRev** is a **VBA** function that returns the end position of a **character**. Weâ€™ll input our **cell **value as **x1** and the specific character (in our case, it is a **forward-slash**) as **x2** in this custom function.

- After that, close the
**VBA**window and go to the â€ś**Position VBA**â€ť**sheet**. - Type the following formula in
**cell D5**.

`=LOccurence(C5,"/")`

In this custom function, weâ€™re telling it to find the position of the **last occurrence** of **forward-slash** in the **string** from **cell C5**.

- Then, press
**ENTER**.

Weâ€™ve got **8** as expected as the **last occurred **position of the **forward-slash**.

- Finally, we can drag the formula down using the
**Fill Handle**.

Thus, weâ€™ve applied yet another formula for finding the position of the **last occurrence** of a **character**.

**Read More:** **How toÂ Find Character in String from Right in Excel (4 Easy Methods)**

### 5. Using Combined Functions in Excel to Find Last Occurrence of Character in String

Up to this, weâ€™ve seen how to find the last occurred position of a character. Now weâ€™re going to use the **SEARCH** function, the **RIGHT **function, the **SUBSTITUTE**, the **LEN**, the **CHAR** functions to show the string after the last occurrence of a character. In simpler terms, we will output the department of the employees from the **Employee Code column**.

**Steps:**

- Firstly, type the following formula in
**cell D5**.

`=RIGHT(C5,LEN(C5)-SEARCH(CHAR(134),SUBSTITUTE(C5,"/",CHAR(134),LEN(C5)-LEN(SUBSTITUTE(C5,"/","")))))`

**Formula Breakdown**

**SUBSTITUTE(C5,â€ť/â€ť,CHAR(134),LEN(C5)-LEN(SUBSTITUTE(C5,â€ť/â€ť,â€ťâ€ť)))**-> becomes,**SUBSTITUTE(C5,â€ť/â€ť,CHAR(134),2)****Output: â€śMike/32â€ Marketingâ€ť**.- The
**SUBSTITUTE**function replaces a value with another value. In our case, it is replacing each**forward-slash**with a**â€**in the first portion and with blank in the latter portion. Then the**LEN**function measures the length of that. That is how we have got our value.

**SEARCH(â€śâ€ â€ť,â€ťMike/32â€ Marketingâ€ť)****Output: 8**.- The
**SEARCH**function is finding the special character in our previous output. Consequently, it found it in**8th**

- Finally, our formula reduces to,
**RIGHT(C5,9)****Output: â€śMarketingâ€ť**.- The
**RIGHT**function returns the cell value up to a certain number of characters from the right side. Weâ€™ve found the position of the last**forward-slash**in**8th**The length of**cell C5**is**17**, and**17**â€“**8**=**9**. Hence, weâ€™ve got the**9****characters**from the right side as the output.

- Secondly, press
**ENTER**.

Weâ€™ve gotten the **strings** after the last **forward-slash**.

- Finally, use the
**Fill Handle**to**AutoFill**the formulas into**cell**range**D6:D10**.

Thus, weâ€™ve extracted the **strings** after the **last occurrence** of a **character**.

**Read More:** **How toÂ Find If Cell Contains Specific Text in Excel**

### 6. Custom VBA Formula in Excel to Find Last Occurrence of Character in String

For the last method, Weâ€™ll use a custom **VBA** formula to extract the **string** after the** forward slash**.

**Steps:**

- Firstly, press
**ALT + F11**to bring up the**VBAÂ**window.

You can choose **Visual Basic** from the **Developer** tab to do so too.

- Secondly, From
**Insert**>>> select**Module**as we did in method**4**. - Thirdly,
**copy**and**paste**the following code.

```
Function LastString(cRange As Range, cString As String)
Dim cLength As Integer
cLength = Len(cRange)
For x = cLength To 1 Step -1
If Mid(cRange, x - 1, 1) = cString Then
LastString = x
Exit Function
End If
Next x
End Function
```

Weâ€™re creating a custom function called â€ś**LastString**â€ť. This function will return the beginning position of the **strings** after the **last occurrence** of a **character.**

- After that, type the formula from below to
**cell D5**.

`=RIGHT(C5,LEN(C5)-LastString(C5,"/")+1)`

**Formula Breakdown**

**LastString(C5,â€ť/â€ť)****Output: 9**.- Here weâ€™re getting the starting position of the
**string**immediately after the**last forward slash**.

**LEN(C5)****Output: 17**.

**LEN(C5)-LastString(C5,â€ť/â€ť)+1****Output: 9.**- We need to add
**1**else weâ€™ll get value with the â€ś**M**â€ť.

- Our formula will reduce to
**RIGHT(C5,9)****Output:**â€ś**Marketing**â€ś.

- Press
**ENTER**.

Weâ€™ll get the value â€ś**Marketing**â€ť.

- Finally,
**AutoFill**the formula up to**cell C10**.

Weâ€™ve achieved our goal. The formula works as intended.

**Read More:** **How to Find Last Row with a Specific Value in Excel (6 Methods)**

**Practice Section**

Weâ€™ve attached practice datasets besides each method in the **Excel** file. You can practice getting better at this task.

**Conclusion**

Weâ€™ve shown you **6** methods in **Excel** to find the **last occurrence** of a **character** in a **string**. If you have any problems regarding these, feel free to comment below. Thanks for reading, and keep excelling!

**Related Articles**

**How to Find External Links in Conditional Formatting in Excel (2 Ways)****How to Find Excel Sheet Name Using Formula (3 Examples)****FIND Function Not Working in Excel (4 Reasons with Solutions)****How to Use Formula to Find Bold Text in Excel****[Solved!] CTRL+F Not Working in Excel (5 Fixes)****[Fixed]: Canâ€™t Find Project or Library Error in Excel (3 Solutions)**