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