Returning part of a cell string to another cell

H

here

In column A I have a listing of subdirectories like "v:\XXXXXXX\YYYY\ZZZ".
In column B I want to return the highest directory level; in this case the
result expected would be "XXXXXXX". Can someone give me a formula to do this
please? By the way, the highest directory level can be any number of
characters, but one can assume the 1st character will always start in the
4th position.

TIA, AlanN
 
T

Trevor Shuttleworth

Alan

one way:

=IF(ISERR(MID(A1,4,FIND("\",A1,4)-4)),MID(A1,4,LEN(A1)-3),MID(A1,4,FIND("\",
A1,4)-4))

or:

=IF(ISERR(FIND("\",A1,4)),MID(A1,4,LEN(A1)-3),MID(A1,4,FIND("\",A1,4)-4))

assuming your file name is cell A1. Caters for the situation where the
second "\" is not found.

Simpler version:

=MID(A1,4,FIND("\",A1,4)-4) if second back slash is always there

Regards

Trevor
 
A

AlanN

Beautiful!

Thanks, Alan


Trevor Shuttleworth said:
Alan

one way:

=IF(ISERR(MID(A1,4,FIND("\",A1,4)-4)),MID(A1,4,LEN(A1)-3),MID(A1,4,FIND("\",
A1,4)-4))

or:

=IF(ISERR(FIND("\",A1,4)),MID(A1,4,LEN(A1)-3),MID(A1,4,FIND("\",A1,4)-4))

assuming your file name is cell A1. Caters for the situation where the
second "\" is not found.

Simpler version:

=MID(A1,4,FIND("\",A1,4)-4) if second back slash is always there

Regards

Trevor
Click to expand...
 
Top