move cells based on group levels

R

research-assistant

I have a grouped data that looks like this:

Column A

02131 SYN.NON-NRC.INJ
DALGAN AZN 90/07
FCD DISP SYR REG IM/IV/SC
15MG/M
5MG/ML
10MG/M
FDD VIAL REGULAR IM/IV/SC
15MG/M
10MG/M
5MG/ML

So line 1 is group level 1, and second indentation is group level 2, and
third indentation is group level 3, and fourth indentation is group level 4.
They are all in the same column.
Is there a way to move second indentation to column B, third indentation to
column C and fourth indentation to column D, so that it looks like the
following:

A B C D

02131 SYN.NON-NRC.INJ
DALGAN AZN 90/07
FCD DISP SYR REG IM/IV/SC
15MG/M
5MG/ML
10MG/M
FDD VIAL REGULAR IM/IV/SC
15MG/M
10MG/M
5MG/ML


The data is massive. And it seems impossible to do it by hand. Thanks a lot.
 
D

Dave Peterson

Maybe...

From your post, it looks like your data has 0 leading spaces in the first level,
2 in the second level, 4 in the next, 6 in the next...

With the data in A1:A###...

I used these formulas:
In B1: =IF(LEFT($A1,1)<>" ",TRIM($A1),NA())
In C1: =IF(ISTEXT(B1),"done",IF(LEFT($A1,3)<>REPT(" ",3),TRIM($A1),NA()))
In D1: =IF(ISTEXT(C1),"done",IF(LEFT($A1,5)<>REPT(" ",5),TRIM($A1),NA()))
In E1: =IF(ISTEXT(D1),"done",IF(LEFT($A1,7)<>REPT(" ",7),TRIM($A1),NA()))

Then I dragged them down the columns.

I waited for the recalc
and converted to values
(edit|copy, edit|paste special values)

Then I used a couple of edit|replaces to get rid of #N/A's and done's
(I replaced them with nothing.)
 
Top