List with hierarchy-new column with parent

[Vilep]

I have a list in a column of Regions. The elements subregions or sub-subregions are identified by "-". There may be one or more levels per region.

I would like to create a column of "Hierarchy of regions", so the "level above being the parent" will show.

The elements are always sorted, so no e.g. row of Europe-England when listing African regions.

Preferably by using "basic formula" in Excel or Power Query.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=TEXTBEFORE(A2,"-",-1,,,"")

 

[fruh]

You can try split:

=TEXTSPLIT(A2;"-")

Result looks like this:

 

[fruh]

You can try split:

=TEXTSPLIT(A2;"-")

Result looks like this:
View attachment 95253

Small mistake: ; should be ,

 

[Fluff]

That does not give the expected results that the OP showed.

 

[Vilep]

=TEXTBEFORE(A2,"-",-1,,,"")

Thanks, and thanks a lot. The formula worked very nicely down the rows on my new (one) column in Excel.
Would you possibly know the formula, if new column created during import of data in Power query?

I've now tried creating Custom columns with "Text.BeforeDelimiter", but it looks to me that I need "extra helping columns like "Text.BeforeDelimiter([Regions],"-",1))" for regions before second "-" to retrieve parts of the original column data and then make a new final. This looks not so smart as your one excel formula.

 

[Fluff]

I know very little about PQ & so cannot help with that.

 

[Vilep]

I know very little about PQ & so cannot help with that.

Thanks. I got the formula nicely.

 

[Fluff]

You're welcome & thanks for the feedback.