Forum Discussion
4 rows of data in 1 cell . Split into 4 cells
Wanted to know how to split data in a cell into multiple cells. I have 4 rows of data in 1 cell and want to split the data into 4 cells. I know what text to column does but this doesn't apply. Also I have no admin rights to add anything ie "Get & Transform (Power Query)" - Looking for a formula
1 cell contains these 4 rows. But now want to split this data into 4 cells VERTICALLY and not horizontally.
ABC 0004
ABC 0020
ABC 0040
ABC 0048
That is approximately the same
=TRIM(MID(SUBSTITUTE($B$4,CHAR(10),REPT(" ",LEN($B$4))),(ROW()-ROW($A$8))*LEN($B$4)+1,LEN($B$4)))in B8 on screenshot and drag down
and attached
8 Replies
Thank you for your response but I have no admin rights to get Get & Transform (Power Query) and the support guys here will not add it for me. If there is no other way pls let me know
Thank you for this but want to split this data into 4 cells VERTICALLY and not horizontally. Pls see attach for example
That is approximately the same
=TRIM(MID(SUBSTITUTE($B$4,CHAR(10),REPT(" ",LEN($B$4))),(ROW()-ROW($A$8))*LEN($B$4)+1,LEN($B$4)))in B8 on screenshot and drag down
and attached
