How to transform one character column with row levels delimited by white spaces into level columnsI 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.
Re: How to transform one character column with row levels delimited by white spaces into level columThanks 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
Re: How to transform one character column with row levels delimited by white spaces into level columThank 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?Re: How to transform one character column with row levels delimited by white spaces into level columI attached a data table to show the approach I described at work. It doesn't handle special cases yet.
Re: How to transform one character column with row levels delimited by white spaces into level columSo you are wanting it to look like this?
If so, I created the columns as formulas and used the Item function to pull the proper pieces out of the Location column.
Re: How to transform one character column with row levels delimited by white spaces into level columYou 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
