Forum home » Delegate support and help forum » Microsoft Excel Training and help » RIght-to-left text searching
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
RIght-to-left text searching
Resolved · Low Priority · Version 2007
Reply Fri 4 Nov 2011, 12:22Delegate Lisa said...
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
For upcoming training course dates see: Pricing & availability
Reply Fri 4 Nov 2011, 15:41Delegate Lisa said...
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
Tue 7 Feb 2012: Automatically marked as resolved.
|