Finding the last occurrence of character in a string in Excel is a nuanced task that can significantly streamline data manipulation and analysis. This capability is crucial when dealing with large datasets where you need to extract specific information from strings, like file paths, URLs, or ID codes. Whether you’re a data analyst sifting through intricate data sets, a marketer segmenting customer information, or an IT professional managing databases, this guide will meticulously walk you through the process of locating the last occurrence of a character in a string in Excel. By mastering this function, you’ll unlock new dimensions of data organization and retrieval, enhancing the efficiency and accuracy of your work.
This Content Covers:
- How do You Find the Last Occurrence of Character in a String in Excel?
- Using Excel’s FIND & SUBSTITUTE Functions
- Using MATCH & SEQUENCE Functions
- Using an Excel Array Formula
- Using VBA
- How to Find the Last Character in a String?
- Using the RIGHT Function
- Using Custom VBA
1. How do You Find the Last Occurrence of Character in a String in Excel?
Now we will show you how you can find the last occurrence of a character in a string using the data from column E of the below-given picture.
1.1 Using Excel’s FIND & SUBSTITUTE Functions
To determine the position of a characters last occurrence in a text string we will use the FIND and SUBSTITUTE functions with CHAR and LEN function.
Step 1: Select cell F2 and insert the following formula inside this cell. This formula will return the last occurrence position of (-) inside this text string. Now click and drag the cell downwards to apply the formula to the other cells also.
=FIND(CHAR(134),SUBSTITUTE(E2,”-“,CHAR(134),(LEN(E2)-LEN(SUBSTITUTE(E2,”-“,””)))/LEN(“-“)))
Step 2: All the cells are now showing the last occurance of (-) in all of these text strings.
1.2 Using MATCH & SEQUENCE Functions to find last occurrence of character
To determine the position of a character’s last occurrence in a text string we will use the MATCH and SEQUENCE function with MID and LEN function in Excel 365 or Excel 2021, as no other versions of Excel have SEQUENCE function in them.
Step 1: Insert the following formula into cell F2 after choosing this cell. The length of cell E2 is determined by the LEN function. The SEQUENCE function returns an array with a list of values in order. The Match function locates the formula’s final 1 value and it is located in the 18th spot as you can see in the picture below.
=MATCH(2,1/(MID(E2,SEQUENCE(LEN(E2)),1)=”-“))
Step 2: To apply the formula to the other cells as well, click and drag the cell downwards. Now we’ve got the position of the last occurrence of this (-) character in all of our strings.
1.3 Using an Excel Array Formula
To identify the location of a character’s last occurrence in a string, we’ll build an array formula using the MATCH, the MID, the ROW, the INDEX, and the LEN function.
Step 1: Insert the below-given formula in cell F2 and click ENTER key if you are using Excel 365. But if you are using any other version you have to press the CTRL+SHIFT+ENTER keys for this formula to work, as in an Array Formula.
=MATCH(2,1/(MID(E2,ROW($E$1:INDEX(E:E,LEN(E2))),1)=”-“))
Step 2: Now use the Fill Handle to auto-fill the formula in all the other cells of this coumn.
1.4 Using VBA
Step 1: Right click on the worksheet and select View Code.
Step 2: Click on the Insert option and select Module.
Step 3: Insert the following code inside VBA Module and then close the Module.
Function CharLastPosition(strVal As String, strChar As String) As Long
CharLastPosition = InStrRev(strVal, strChar)
End Function
Step 4: Select cell F2 and insert the formula given below. Click Enter key.
=CharLastPosition(E2,”-“)
Step 5: Use the Fill-Handle to autofill the formula in the other cells of this column to get the last occurance of (-).
2. How to Find Last Character in String?
Using the RIGHT function and a VBA Code, you can easily extract everything to the right of the last occurrence of a text in a string.
2.1 Using RIGHT Function
Step 1: Insert the formula given below in F2 and click Enter key.
=RIGHT(E2,LEN(E2)-FIND(“@”,SUBSTITUTE(E2,”-“,”@”,LEN(E2)-LEN(SUBSTITUTE(E2,”-“,””))),1))
Step 2: To apply the formula to the other cells as well, click and drag the cell downwards. This will give you the last position of (-) and extract all the text to the right of it.
2.2 Using Custom VBA
Step 1: Go to Developer tab and click on Visual Basic or Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
Step 2: Select Insert>>Module.
Step 3: Paste the code given below inside VBA Module and close it.
Function LastPosition(rCell As Range, rChar As String)
‘This function gives the last position of the specified character
‘This code has been developed by Sumit Bansal (https://trumpexcel.com)
Dim rLen As Integer
rLen = Len(rCell)
For i = rLen To 1 Step -1
If Mid(rCell, i – 1, 1) = rChar Then
LastPosition = i
Exit Function
End If
Next i
End Function
Step 4: Now insert this below given formula in F2 and click Enter key.
=RIGHT(E2,LEN(E2)-LastPosition(E2,”-“)+1)
Step 5: To automatically fill the formula in the other cells in this column, use the Fill-Handle. This will extract all the text to the right of it (-).
Application of Find the Last Occurrence of Character in a String in Excel
- File Extension Extraction: Identify and extract the file extension from filenames by finding the position of the last period (.) character in each filename.
- Domain Name Retrieval: Retrieve the top-level domain (TLD) from email addresses or website URLs by locating the last occurrence of the dot (.) character.
- Data Tagging and Categorization: Extract tags or categories from strings where data is separated by a specific delimiter, such as the last underscore (_) or dash (-).
- Path Extraction in File Locations: Find the last occurrence of a backslash () to extract the file name from a full file path, separating file names from directories.
- Unit of Measurement Identification: Identify and extract units of measurement from product names or descriptions by finding the last space or specific character preceding the unit (e.g., “kg,” “cm”).
- Name Suffix Extraction: Extract suffixes from names (e.g., Jr., Sr., III) by finding the last occurrence of a space or comma, helping in data cleaning and organization.
For ready-to-use Dashboard Templates:
