Lisa has attended:
Excel Advanced course
RIght-to-left text searching
Is there any way to make Excel search a text string from right to left, rather than left to right?
I have a series of file paths (eg G:\My Documents\Lisa\document.doc) where the path lengths and file names are different lengths and I would like to extract the file name (eg document.doc).
Ideally, I would use some combination of =RIGHT and =FIND to tell it pull the text after it has found '\' by searching right to left but I don't see how to do this.
Any thoughts appreciated.
Cheers,
Lisa
RE: RIght-to-left text searching
I'm still curious as to the answer, but I have found a workaround. For anyone interesed, I knew the file names were all around 6/7 folders deep so I did a kind of nested find function with some iferrors to cover a variety of folder lengths. Not the prettiest and not very flexible so other solutions welcome for future!
=IFERROR(IFERROR(RIGHT(B2,LEN(B2)-FIND("\",B2,FIND("\",B2,FIND("\",B2,FIND("\",B2,FIND("\",B2,FIND("\",B2)+1)+1)+1)+1)+1)),RIGHT(B2,LEN(B2)-FIND("\",B2,FIND("\",B2,FIND("\",B2,FIND("\",B2,FIND("\",B2)+1)+1)+1)+1))),RIGHT(B2,LEN(B2)-FIND("\",B2,FIND("\",B2,FIND("\",B2,FIND("\",B2)+1)+1)+1)))
Happy to talk through how it works if anyone is interested!
RE: RIght-to-left text searching
Hello Lisa,
Here's another alternative way of getting the name from the right,
=TRIM(RIGHT(SUBSTITUTE(E12,"\",REPT(CHAR(32),LEN(E12))),LEN(E12)))
Change E12 to the cell your path is in, hope it works.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
