Excel Find Last Occurrence of Character in String (8 Methods)

Our sample dataset has three columns: Company Name, Employee Code, and Last Occurrence. Employee Code contains the name, age, and department of an employee. For the first 5 methods, we’ll find the position of the forward-slash “/” in for all the values in the Employee Code. After that, we’re going to output strings after the last slash in the last 3 methods.

Excel Find Last Occurrence of Character in String: 8 Methods

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

Let’s extract the position of the final slash in the codes from the dataset.

Steps:

• Copy the following formula in cell D5.

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

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

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

The SEQUENCE function is only available on Excel 365 or Excel 2021. We’ll use the same dataset.

Steps:

• Insert the following formula in cell D5:

• Press Enter.

Using the formula, we’ve found the position of forward-slash as 8 in our string.

• Use the Fill Handle to AutoFill the formula.

This finds the last position of a character in the strings.

Read More: How to Find Character in String Excel

Method 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:

• Input the formula below into cell D5:

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

• Double-click or drag down the Fill Handle.

• This is what the final result should look like.

Read More: How to Find Character in String from Right in Excel

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

Steps:

• Press Alt + F11 to bring up the VBA window. You can choose Visual Basic from the Developer tab, as well.
• From Insert, select Module.

• Copy and paste the following code into the module:

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.

• Close the VBA window and go to the “Position VBA” sheet.
• Type the following formula in cell D5:

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.

• Press Enter.

We’ve got 8 as expected as the last occurred position of the forward-slash.

• Drag the formula down using the Fill Handle.

• Here’s the final result.

Read More: How to Find from Right in Excel

Method 5 – Using LEN, TAKE & TEXTSPLIT Functions to Find Position of Last Occurrence of Character in String in Excel

The TAKE and TEXTSPLIT functions are only available in Excel for Microsoft 365.

Steps:

• Insert the following formula in cell D5:

• Press the Enter key and drag down the Fill Handle icon.

• Here is the final output of the last occurring positions of the “/” character.

Method 6 – Using Combined Functions in Excel to Find Last Occurrence of Character in String

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, so we’ll output the department of the employees from the Employee Code column.

Steps:

• Insert the following formula in cell D5:

• Press Enter.

We’ve gotten the strings after the last forward-slash.

• Use the Fill Handle to AutoFill the formulas into cell range D6:D10.

• This is the final result.

Method 7 – Applying TAKE & TEXTSPLIT Functions in Excel to Find Last Occurrence of Character in String

This method is similar to method 5.

Steps:

• In cell D5, copy and paste the following formula:

• Press the Enter key, then double-click on the Fill Handle icon.

• Here is the final output of the strings that appear after the last occurring positions of the “/” character.

Method 8 – Custom VBA Formula in Excel to Find Last Occurrence of Character in String

Let’s extract the department using a custom formula.

Steps:

• Press Alt + F11 to bring up the VBA window.
• From Insert, select Module.
• Copy and paste the following code into the module window.

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.

• Save the module.
• Copy the formula from below to cell D5:

• Press Enter.

We’ll get the value “Marketing”.

• AutoFill the formula up to cell C10.

• Here’s the result.

Practice Section

We’ve attached practice datasets beside each method in the Excel file.

Download Practice Workbook

Related Articles

• How to Find Text in Cell in Excel
• How to Check If Cell Contains Specific Text in Excel
  
• How to Find If Range of Cells Contains Specific Text in Excel
• How to Find * Character Not as Wildcard in Excel

<< Go Back to Find in String | String Manipulation | Learn Excel