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.
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:
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: 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: 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.
How to Get the Word Count in Excel. How to Use VLOOKUP with Multiple Criteria. Find the Last Occurrence of a Lookup Value a List in Excel. Extract Substring in Excel.
=RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”5.)”,”@”,(LEN(A2)-LEN(SUBSTITUTE(A2,”5.)”,””)))/3),1)) [new] InStrRev(rCell, rChar) + 1 InStrRev(MyString, “/”) + 1 ? Example: Input: ABC123BCDSample.txt Expected output: ABC123BCD
