word up to last "\"

[gsrikanth]

in first column i have path
for example c:\a\b\c\d\e.jpg output c:\a\b\c\d\

any another example

\\gis-9\D\ARUNACHALPRADESH\ARUNACHAL\RAIGAM\info\arc000. dat



i need upto \\gis-9\D\ARUNACHALPRADESH\ARUNACHAL\RAIGAM\info\

[Colin Legg]

Here's one way

=LEFT(A1,SEARCH("\ /",SUBSTITUTE(A1,"\","\ /",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

The formula will return a #VALUE! error if there are no \ characters in the cell being checked.

[konopca]

Hello
This is just what I was looking for, but I need everything after the last slash. Can you help?
Thanks
Carol

[Colin Legg]

Hi Carol,

Here's one way:

=RIGHT(A1,SEARCH("\ /",SUBSTITUTE(A1,"\","\ /",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+LEN("\ /")-1)

[konopca]

Colin -
It does not seem to work. I don't know what I am getting, column B seems to have variable amounts of data and only parts of words.
My column A has different numbers of slashes in each row (you know different levels of subfolders). Does this matter?
Carol

[Colin Legg]

Hi,

Sorry, don't know what happened there....

=RIGHT(A1,LEN(A1)-SEARCH("\ /",SUBSTITUTE(A1,"\","\ /",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

[konopca]

Thank you, that did it.