How to apply the reverse find or search function in Excel?
The standard Find or Search functions in Excel are designed to locate text from left to right within a string, making it straightforward to extract information that appears early in your data. However, in certain scenarios—such as processing log entries, addresses, or any data where critical information is listed at the end—you may need to reverse the approach and identify or extract the last item based on a specific delimiter. For instance, you might want to extract the last word, number, or phrase after a particular character in a list, as shown in the screenshot below.
Apply the reverse find function to look for the word from right in text string with formula
Use VBA to perform reverse search with any delimiter
Apply the reverse find function to look for the word from right in text string with formula
Excel provides a practical formula-based method to extract the last word or segment from a text string using a specified delimiter. In many cases, the delimiter is a space character, which makes this approach especially useful when working with names, descriptive texts, or word lists.
Here’s how you can extract the last word from a cell (assuming the delimiter is a space):
Enter this formula in a blank cell (for example, B2). Then, press Enter to confirm. To apply this for multiple rows, drag the fill handle down, and the last word in each text string will be extracted immediately. See screenshot:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ",99)),99))
Note: This formula works specifically with space characters as delimiters. If your data uses another character (such as a dash, comma, or other symbol), the formula needs to be adapted.
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), "|", REPT(" ",99)),99))Use VBA to perform reverse search with any delimiter
For advanced scenarios such as variable-length delimiters, or extracting words/items under more complex criteria (such as ignoring empty values, handling nested delimiters, or processing very large text strings), using a custom VBA macro can be an efficient and flexible solution. VBA lets you automate extraction tasks with any delimiter and custom rules, including searching for patterns from right to left.
1. To use VBA, first open the VBA editor: Click Developer Tools > Visual Basic. The Microsoft Visual Basic for Applications window will appear. Click Insert > Module and copy the code below into the new Module:
Sub ReverseFindLastItem()
Dim rng As Range
Dim inputRange As Range
Dim delimiter As String
Dim cell As Range
Dim splitArray() As String
Dim i As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set inputRange = Application.Selection
Set inputRange = Application.InputBox("Select range to process", xTitleId, inputRange.Address, Type:=8)
delimiter = Application.InputBox("Enter your delimiter (e.g., comma, pipe, dash)", xTitleId, ",", Type:=2)
For Each cell In inputRange
If cell.Value <> "" Then
splitArray = Split(cell.Value, delimiter)
cell.Offset(0, 1).Value = splitArray(UBound(splitArray))
End If
Next cell
End Sub2. Click the 
button to execute the code. A dialog box will prompt you to select your target range, followed by input for your delimiter. After running, the last item after the delimiter in each cell is output to the cell immediately right of each original value.
Error reminder: If you do not specify a valid delimiter, or your cell is empty, the macro will skip the cell with no changes. Always verify output to ensure correct extraction, especially with unusual or complex data.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in