You have an Excel file with thousands of rows like in the below image and for each row you need to extract the file name:
How would you do this ?
Solution:
One formula that can solve your problem is this one:
=RIGHT(A3,LEN(A3)-FIND(“$”,SUBSTITUTE(A3,”/”,”$”,LEN(A3)-LEN(SUBSTITUTE(A3,”/”,””)))))
Let’s explain how it works:
The file name is the sub-string after the last occurrence of the “/” character in the initial string and until the end. So if we will somehow be able to find the position of the last “/” character in the initial string we could easily get the file name by using something like: =Right(“initial string”,len(“initial string”)-“position of last ‘/’ in the string”).
We can get the last occurrence of the “/” character by replacing all the “/” characters in the initial string with nothing. Then, the length of the initial string minus the length of the string without “/” characters will give us the number of the “/” characters in the initial string. The total number of “/” is also the last occurrence of this character in the string 🙂
So, we can do this by using the Excel Substitute function:
Now that we know the number of last occurrence of the “/” character in our string, we can replace this with another character in order to be able to distinguish it from the other “/” characters that we have in the same string. The excel Substitute function has an optional parameter named “instance_num” that can be used to substitute only the desired instance number of a character in a string. We can use it in order to replace the 7th “/” character (in our example) with some other unique character (for example the “$” will do the work):
Then, we can simply find out the position of the “$” in the new string using the Find or Search functions and get the file name with the Right function (or Mid would work as well):
If we combine all above formulas into one we can use the following:
Download the SAMPLE EXCEL FILE
PS: If you would like to do this with VBA, there is a nice function called INSTRREV which returns the position of the first occurrence of a string in another string, starting from the end of the string. Here you can see a sample screenshot of some VBA code that I used in a project in order to quickly get only the text after the last “|” character in a given range of cells:
Sub strings_without_cat() Dim lr As Long Dim sht As Worksheet Set sht = ActiveSheet lr = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row For i = 2 To lr For j = 3 To 8 Application.StatusBar = i & "/" & lr If Trim(Cells(i, j)) <> "" Then 'the position of the last "|" lastchr_pos = InStrRev(Trim(Cells(i, j)), "|") 'lenght from the last "|" and until de end of string lungime = Len(Trim(Cells(i, j))) - lastchr_pos 'only the part from the last "|" and until de end of the original string final_string = Right(Trim(Cells(i, j)), lungime) Cells(i, j) = final_string End If Next j Next i MsgBox "Completed", vbInformation End Sub
