Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

righttoleft text searching

Forum home » Delegate support and help forum » Microsoft Excel Training and help » RIght-to-left text searching

RIght-to-left text searching

ResolvedVersion 2007

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

Tue 7 Feb 2012: Automatically marked as resolved.

Excel tip:

Quickly select a block of data

To quickly select a block of data make sure your active cell is somewhere whithin the block of data and then press Ctrl+* or Ctrl+Shift+8.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.