Unlike some programming languages that have built-in functions for this task, Excel does not have a direct formula to find the last occurrence of a character. However, you can achieve this using functions like SEARCH, FIND, RIGHT, LEN, and SUBSTITUTE.
A common way to find the last occurrence find last occurrence of character in string excel of a character (e.g., a comma, space, or hyphen) is by using the following formula:
LEN(A1): Finds the total length of the string in cell A1.
SUBSTITUTE(A1, "-", ""): Removes all occurrences of the target character (“-” in this case).
LEN(SUBSTITUTE(A1, "-", "")): Finds the new length after removal.
Subtracting the new length from the original length gives the number of occurrences of the character.
Adding 1 gives the position of the last occurrence.
2. Using SEARCH with SUBSTITUTE for Dynamic PositioningAnother effective way is using a formula that dynamically determines the last position of a character:
sqlCopyEdit=SEARCH("#", SUBSTITUTE(A1, "-", "#", LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))))How It Works:
SUBSTITUTE(A1, "-", "") counts how many times the character appears.
Replaces only the last occurrence of "-" with a special character ("#").
Uses SEARCH to find the position of this newly inserted character.
3. Using VBA (Advanced Approach)For those who frequently need to find the last occurrence of a character, using a VBA function can make this task easier.
Steps to Create a VBA Function:Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Click Insert > Module.
Paste the following code:
vbaCopyEditFunction FindLastOccurrence(ByVal txt As String, ByVal char As String) As IntegerDim i As IntegerFor i = Len(txt) To 1 Step -1If Mid(txt, i, 1) = char ThenFindLastOccurrence = iExit FunctionEnd IfNext iFindLastOccurrence = 0 ' Return 0 if character is not foundEnd FunctionSave the module and close the VBA editor.
Use the function in Excel like this:
arduinoCopyEdit=FindLastOccurrence(A1, "-")Use CasesExtracting the file extension from a filename:
