I want to move Data presided by a space to a new column

[JamesG2806]

I want to move Data presided by a space to a new column. So for an example if I had the below image attached: 

 So the image below what I am looking for is Data 1 to remain in column A, Item 1 to be moved to column B as this has one space and Sub 1 to be moved to column c as this has two spaces and so on. 

[cmcclellan]

The formula for B would be:

if left([A],1) = " " then [A] else null() endif

The formula for C would be:

if left([A],2) = "  " then [A] else null() endif

BUT ... it looks like you're trying to pivot a hierarchy and the above will leave you with null cells in each column as well.  Is that what you're after or do you want all the values on a single row ?

And how many rows do you have in your real source file ?

[JamesG2806]

So attached the real sheet here now. So its been pulled from accounting software and the spaces are basically the sub categories. That is why I am trying to shift them over and then say for example A2 - Assets would drop down and automatically fill till we got to A35 Non assets. 

[cmcclellan · Accepted solution]

i think you're after something like this ? (workflow attached)

[JamesG2806]

Perfect thanks a million for your help