0

I'm in Excel and I have a long list of folder directories like you see below. I want to use the forward slash delimiter to only keep only the file name in the directory. The problem is that each directory link has it's own depth of sub-folders that make it tricky to extract just the the file name at the end. Is there a way to do this?

What it looks like now:

../folder1/file1.pdf
../folder1/folder2/folder3/file4.pdf
../folder1/folder2/file3.pdf

What I want it to look like:

file1.pdf
file4.pdf
file3.pdf

shampouya
  • 386
  • 1
  • 6
  • 24

1 Answers1

2

FILTERXML() would be good choice in this case. Try-

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[last()]")

You can also use below formula-

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),100))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36