Returning part of a cell string to another cell

  • Thread starter Thread starter here
  • Start date Start date
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
 
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
 
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...
 
Back
Top