Excel: How to Find Last Occurrence of Character in String

You can use the following formula in Excel to find the last occurrence of a specific character in a string:

=LEN(TEXTBEFORE(A2,"/",-1))+1

This particular formula finds the position of the last occurrence of a slash ( / ) in cell A2.

The following example shows how to use this formula in practice.

Example: Find Last Occurrence of Character in String in Excel

Suppose we have the following column of phrases in Excel:

Suppose we would like to find the position of the last occurrence of a slash ( / ) in each cell in column A.

We can type the following formula into cell B2 to do so:

=LEN(TEXTBEFORE(A2,"/",-1))+1

We can then click and drag this formula down to each remaining cell in column B:

Column B now contains the position of the last occurrence of a slash ( / ) in each cell in column A.

For example, consider the first string in cell A2:

The position of the last occurrence of a slash in this string is in position 10:

Recall the formula that we used to find the position of the last occurrence of a slash in cell A2:

=LEN(TEXTBEFORE(A2,"/",-1))+1

This formula uses the TEXTBEFORE function, which uses the following basic syntax:

TEXTBEFORE(text, delimiter, [instance_num], …)

where:

text: Text to search
delimiter: Character or substring to extract text before
instance_num: Instance of delimiter before which to extract text (default is 1)

By using TEXTBEFORE(A2, “/”,-1) we are able to extract the following text:

Then we use the LEN function to get the length of this text, which returns 9.

Lastly, we add one to get 10.

This represents the position of the last occurrence of a slash in cell A2.

Note: You can find the complete documentation for the TEXTBEFORE function in Excel here.

The following tutorials explain how to perform other common tasks in Excel: