String: "apple, banana, cherry, date"

If we want to find the last occurrence of the comma (",") in the above string, the expected output should be 21, since the  find last occurrence of character in string  last comma appears before "date."

Methods to Find the Last Occurrence of a Character

1. Using a Combination of SEARCH and SUBSTITUTE

One of the most effective ways to find the last occurrence of a character is by using the SEARCH and SUBSTITUTE functions. The following formula is commonly used:

=SEARCH("#",SUBSTITUTE(A1, ",", "#", LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))))

Explanation:

SUBSTITUTE(A1, ",", "") removes all instances of the comma and returns a version of the string without them.

LEN(A1) - LEN(SUBSTITUTE(A1, ",", "")) counts the number of commas in the string.

SUBSTITUTE(A1, ",", "#", count) replaces only the last comma with "#".

SEARCH("#", ...) finds the position of "#", which is the last occurrence of the comma.

2. Using FIND, LEN, and SUBSTITUTE

If the character we are searching for is not case-sensitive, we can use the FIND function instead of SEARCH. However, the logic remains similar:

=FIND("#",SUBSTITUTE(A1, ",", "#", LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))))

Both SEARCH and FIND work similarly, but SEARCH is case-insensitive, while FIND is case-sensitive.

3. Using LOOKUP with ROW and MID

Another efficient approach uses LOOKUP, ROW, and MID functions:

=LOOKUP(2,1/(MID(A1,ROW($1:$100),1)=","),ROW($1:$100))

Explanation:

ROW($1:$100) generates an array of row numbers from 1 to 100 (assuming a maximum string length of 100 characters).

MID(A1,ROW($1:$100),1) extracts each character from A1.

MID(A1,ROW($1:$100),1) = "," creates an array of TRUE and FALSE values where TRUE represents the comma positions.

1/(...) converts TRUE values to 1 and FALSE values to errors.

LOOKUP(2,1/(...)) finds the last position of a valid 1 value, which corresponds to the last comma.

4. Using VBA for More Flexibility

For users comfortable with Visual Basic for Applications (VBA), a custom function can simplify the process:

Function LastPositionOfChar(ByVal str As String, ByVal char As String) As Integer
Dim i As Integer
For i = Len(str) To 1 Step -1
If Mid(str, i, 1) = char Then
LastPositionOfChar = i
Exit Function
End If
Next i
LastPositionOfChar = 0 'Return 0 if the character is not found
End Function

To use this function in Excel:

Open VBA Editor (ALT + F11)

Insert a new module

Paste the function

Use it in Excel like a regular formula:

=LastPositionOfChar(A1, ",")

This method is efficient and works well with large datasets.

Choosing the Best Method

Each method has its pros and cons:

SEARCH/SUBSTITUTE: Works well for general cases but may be tricky for very large strings.

LOOKUP/MID/ROW: Can be effective but may slow down performance in large datasets.

VBA Function: Provides a flexible and reusable solution but requires enabling macros.