# Find Position of the Last Occurrence of a Character in a String in Excel

In this tutorial, you’ll learn how to find the position of the last occurrence of a character in a string in Excel.

A few days ago, a colleague came up with this problem.

He had a list of URLs as shown below, and he needed to extract all the characters after the last forward slash (“/”).

So for example, from https://example.com/archive/January he had to extract ‘January’. It would have been really easy has there been only one forward slash in the URLs.

What he had was a huge list of thousands on URLs of varying length and a varying number of forward-slashes.

In such cases, the trick is to find the position of the last occurrence of the forward slash in the URL.

In this tutorial, I will show you two ways to do this:

• Using an Excel formula
• Using a custom function (created via VBA)

## Getting the Last Position of a Character using Excel Formula

When you have the position of the last occurrence, you can simply extract anything on the right of it using the RIGHT function.

Here is the formula that would find the last position of a forward slash and extract all the text to the right of it.

```=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))
```

#### How does this formula work?

Let ‘s break down the formula and explain how each part of it works.

• SUBSTITUTE(A2,”/”,“”) This part of the formula replaces the forward slash with an empty string. So for example, In case you want to find the occurrence of any string other than the forward slash, use that here.
• LEN(A2)-LEN(SUBSTITUTE(A2,”/”,“”)) – This part would tell you how many forward slashes are there in the string. It simply subtracts the length of the string without the forward slash from the length of the string with forward-slashes.
• SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))) – This part of the formula would replace the last forward slash with @. The idea is to make that character unique. You can use any character you want. Just make sure it’s unique and doesn’t appear in the string already.
• FIND(“@”,SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1) – This part of the formula would give you the position of the last forward slash.
• LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1) – This part of the formula would tell us how many characters are there after the last forward slash.
• =RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1)) – Now this would simply give us the string after the last forward slash.

## Getting the Last Position of a Character using Custom Function (VBA)

While the above formula is great and works like a charm, it’s a bit complicated.

If you’re comfortable using VBA, you can use a custom function (also called a User Defined Function) created via VBA. This can simplify the formula and can save time if you have to do this often.

Let’s use the same data set of URLs (as shown below): For this case, I have created a function called LastPosition, that find the last position of the specified character (which is a forward slash in this case).

Here is the formula that will do this:

`=RIGHT(A2,LEN(A2)-LastPosition(A2,"/")+1)` You can see that this is a lot simpler than the one we used above.

Here is how this works:

• LastPosition – which is our custom function – returns the position of the forward-slash. This function takes two arguments – the cell reference that has the URL and the character whose position we need to find.
• RIGHT function then gives us all the characters after the forward slash.

Here is the VBA code that created this function:

```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```

To make this function work, you need to place it in the VB Editor. Once done, you can use this function like any other regular Excel function.

Here are the steps to copy and paste this code in the VB back-end:

Here are the steps to place this code in the VB Editor:

1. Go to the Developer tab. 2. Click on the Visual Basic option. This will open the VB editor in the backend. 3. In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.
4. Go to Insert and click on Module. This will insert a module object for your workbook. 5. Copy and paste the code in the module window. Now the formula would be available in all the worksheets of the workbook.

Note that you need to save the workbook as the .XLSM format as it has a macro in it. Also, if you want this formula to be available in all the workbooks you use, you can either save it the Personal Macro Workbook or create an add-in from it.

You May Also Like the Following Excel Tutorials: FREE EXCEL BOOK

## Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

### 25 thoughts on “Find Position of the Last Occurrence of a Character in a String in Excel”

1. Here is the non-VBA using LET
=LET(
Rmv_F_Slash, SUBSTITUTE(\$B10,””,””),
Num_F_Slash, LEN(\$B10)-LEN(Rmv_F_Slash),
Rplc_Last, SUBSTITUTE(\$B10,””,”@”,Num_F_Slash),
Psntn_Last, FIND(“@”,Rplc_Last),
Num_Chr_Aftr, LEN(\$B10)-Psntn_Last,
Full_Formula, RIGHT(\$B10, Num_Chr_Aftr),
Result, Full_Formula,
Result)

2. thank you

3. If you are searching for more than one character, for example “5.)”, in place of “/” in the equation example above, be sure to divide the length subtraction by the number of characters. This is because for every instance of that character string, that many characters are being removed from the string per instance, so the length subtraction equation gives you a difference that is *length of desired character string* greater than the number of instances.
E.g.,
=RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1)) [original]

=RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”5.)”,”@”,(LEN(A2)-LEN(SUBSTITUTE(A2,”5.)”,””)))/3),1)) [new]

4. Perfect! Thank you so much!! Saved me a lot of time:)

5. thank you

6. Thank you – used the cell formula and it worked a treat !

7. Or Excel specific:

InStrRev(rCell, rChar) + 1

8. Is the LastPosition() function not replaceable with…

InStrRev(MyString, “/”) + 1

?

9. I am so grateful. I was very hungry to get the formula. It will save a hundred hours of mine.

10. Works great! Thanks!

11. You guys just saved me a bunch of time – thank you!

12. Need help on formula to reverse the above process in reverse way,

Example:
Input: ABC123BCDSample.txt

Expected output: ABC123BCD

• Using the example above, I used =MID(A2, 1, FIND(“@”,SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1)-1)
You can remove “-1” if you want to include the last / in the output

• This will remove everything after the final / in A1. Put this in in B1:
=SUBSTITUTE(A1,TRIM(LEFT(RIGHT(SUBSTITUTE(A1,”/”,REPT(” “,100)),100),100)),””)

13. Love it! Worked quick and easy !

14. Great explanation. Well Done and Thank you.

15. Thank you for this formula, this has saved me so much time today!

16. Excellent – thanks!

17. thanks for this, works great

18. sorry, doesn’t work

• It does work you dummy, if you change the cell addresses correctly

19. Thanks so much for this — very helpful!

20. Worked great for me! Thank you!

21. The Excel formula is clever. But the VBA solution can be simplified by using the InStrRev function.

22. Works pretty well, but breaks if there’s only one of the separator character (“/”) in the string.