Extract data from a cell to multiple rows

[liquidtrails]

I have a large spreadsheet that has several numbers in a single column. I would like to create a new row for every number in the column, while maintaining the other columns data, until every column has only one number. Due to proprietary reasons, I cannot provide the workbook so I will do my best to give an example of the issue,

What I have:

1 23, 43, 53 6 8 9 (The 23,43, and 53 are in the same column; everything else is in its own column)

What I would like to create:

1 23 6 8 9
1 43 6 8 9
1 53 6 8 9

I hope this makes sense, and someone can help me.

[patel45]

1 23, 43, 53 6 8 9 (The 23,43, and 53 are in the same column; everything else is in its own column)

It's not clear for me, attach a sample file or image

[markos97]

Hi liquidtrails on forum,
It's so clear for me, try the attach;

1. firstly I copy the cell B1 to sheet2 (Arkusz2)>select column A:A>text as column, and we have the separated numbers
2. copy this to sheet3 (Arkusz3) and transpose 90 degrees, and copy the rest without transpose
3. now select the cell A1, and range C1:E1 and use the recorded code in ThisWorkbook
4.done!

Regards
Mark

[jindon]

UDF

Select available cells 3 rows x 5 columns.
Enter =SeparateRange(A1:E1)
Confirm with Ctrl + Shift + Enter (Array formula entry)

Please Login or Register  to view this content.

[liquidtrails]

Thanks for all the help!!! Jindon yours is awesome, and markos97 yours works well too. Thanks again!