Subscribe Bookmark RSS Feed

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

jenssjorslev

Community Trekker

Joined:

Feb 5, 2015

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. 

Screen Shot 2017-02-08 at 17.56.42.jpg

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
markbailey

Staff

Joined:

Jun 23, 2011

Solution

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

Learn it once, use it forever!
markbailey

Staff

Joined:

Jun 23, 2011

Solution

I attached a data table to show the approach I described at work. It doesn't handle special cases yet.

Learn it once, use it forever!
5 REPLIES
markbailey

Staff

Joined:

Jun 23, 2011

Solution

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

Learn it once, use it forever!
Dan_Obermiller

Joined:

Apr 3, 2013

So you are wanting it to look like this?

Capture.PNG

 

 

If so, I created the columns as formulas and used the Item function to pull the proper pieces out of the Location column.

Dan Obermiller
jenssjorslev

Community Trekker

Joined:

Feb 5, 2015

Thank you. I've tried it but wouldn't work for me for the lowest level. Probably I'd entered the wrong number of white spaces in the Item formula.
However, is there a way to fill the columns for each row? Some Row subscript?
markbailey

Staff

Joined:

Jun 23, 2011

Solution

I attached a data table to show the approach I described at work. It doesn't handle special cases yet.

Learn it once, use it forever!
jenssjorslev

Community Trekker

Joined:

Feb 5, 2015

Thanks a lot. Very smart with the formula Length, and the Lag functions. Can see I need to read more about these functions, especially the Lag function. Cheers, Jens