excelexcel-formulaessbasevba

Separating data set by how many spaces are before first letter

I have a huge data set that I need to separate into a hierarchy. Currently the only way to tell which level the data point is in the hierarchy is how many spaces are before the first letter (It is from an Essbase pull). I need to separate it out into various columns so that I can see the structure more effectively. There are 7 different numbers of spaces (the separation between hierarchy levels). I honestly have no idea how to get this done. Does anyone have any thoughts or advice?

Solution

  • You can use this formula:

    =IF(COLUMN(A:A)=FIND(LEFT(TRIM($A1),1),$A1),TRIM($A1),"")

    Drag across and down.

    enter image description here

    If you do not want 15 - 40 spaces and it appears that all are multiples of 5 you can do this:

    =IF(COLUMN(A:A)=INT(FIND(LEFT(TRIM($A1),1),$A1)/5),TRIM($A1),"")