How to transform one character column with row levels delimited by white spaces into level columns

jenssjorslev

I have a table with a structure of location names like the first column in below table that I want to get into separate columns. The geographical/administrative levels are defined by the use of white space before the entry. It is probably from a printed version of a data set. Have tried various ways, e.g., using UPPERCASE to select higher levels, and put them into columns. But it doesn't work for the lowercases, and is only a half-way solution. Is there a smart way to transfer the entries at each row level to separate columns as shown in the table? I'd prefer an interactive explanation if possible. Thanks a lot for any help. 

 

Mark_Bailey

You can use column formulas. They will either copy a new level from the left in the current row or a repeating value from above the previous row. You can use the amount of white space (indentation) to determine which column should use the value. You will have some missing values for the intial rows and below where the previous column resets