0

I have hierarchy path values in column A of an Excel spreadsheet:

FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM \ COMPRESSOR - DRYER
FLEET \ AIR SYSTEM \ VALVES
FLEET \ AIR SYSTEM \ RESERVOIRS
FLEET \ AIR SYSTEM \ HOSES - LINES - FITTINGS

FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR \ BODY PANELS
FLEET \ BODY EXTERIOR \ WIPERS
FLEET \ BODY EXTERIOR \ MIRRORS
FLEET \ BODY EXTERIOR \ ACCESSORIES
FLEET \ BODY EXTERIOR \ ACCESSORIES \ CATEGORY 1
FLEET \ BODY EXTERIOR \ ACCESSORIES \ CATEGORY 2

I would like to split each string at the last instance of \ and retain the left-hand side of the split string.

[null]
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM

FLEET
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR \ ACCESSORIES
FLEET \ BODY EXTERIOR \ ACCESSORIES

Is it possible to do this via an Excel 2016 formula?

Improve this question

2 Answers 2

Reset to default
2 
=IFERROR(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1),"")

In your comment, you indicate you want to remove the terminal space which is present in the original data. You merely need to use the TRIM function in the formula:

=TRIM(IFERROR(LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,"\",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))))-1),""))

and fill down

enter image description here

If your version of Excel has the FILTERXML and TEXTJOIN functions, you can use the following (which will not have a terminal space).

=IFERROR(TEXTJOIN("\",TRUE,FILTERXML("<t><s>" & SUBSTITUTE(A1&"\","\","</s><s>") & "</s></t>","//s[position()<last()-1]")),"")
Improve this answer
4
  • Thanks! It looks like that formula returns a space at the end of the string. Is there a way to avoid this?
    – User1974
    May 1, 2020 at 1:48
  • @User1973 It is returning a space because that was in your specification -- everything to the left of the \` and there is a space before the \`. If you want to also remove that terminal space, just use the TRIM function around the formula. See Edits
    – Ron Rosenfeld
    May 1, 2020 at 1:53
  • Thanks. I suppose, alternatively, I could use -2 instead of -1 to trim the space.
    – User1974
    May 1, 2020 at 2:00
  • 1
    @User1973 You could, if you were certain that there would always be a space before the final backslash. Using TRIM (or the second formula if you can) eliminates that variation as a possible issue.
    – Ron Rosenfeld
    May 1, 2020 at 2:02
2

You can also try a User Defined Function:

Function LPath(Target As Range) As String

LPath = Trim(Left(Target.Value, InStrRev(Target.Value, "\") - 1))

End Function

Place in a module using ALT F11 to enter developer and insert module, use: LPath(A1) If you want to keep the final "\" then remove the "-1"... if you want to keep the trailing space " " remove the trim function.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .