RIght-to-left text searching

Resolved · Low Priority · Version 2007

Fri 4 Nov 2011, 12:22 Delegate 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

Fri 4 Nov 2011, 15:41 Delegate 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!

Tue 31 Jan 2012, 15:11 Trainer Mark said...

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.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Highlighting only Text cells

To select onlt text value cells in a spreadsheet, click on Edit-Go to(F5)

In the Go To dialog box, click Special.
In the Go To Special dialog box, select Constants.
Click OK.

View all Excel hints and tips