Convert values in 1 cell to rows

Closed
abhishek643
Posts
3
Registration date
Thursday September 25, 2014
Status
Member
Last seen
September 26, 2014
- Sep 25, 2014 at 10:03 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Sep 29, 2014 at 12:01 PM
Hello,

I have an excel in which there are multiple values in 1 cell separated with comma.

i want all the values in cell to be converted in to different rows.

i.e. cell contains value 1,2,3,4,5

then output should be
1
2
3
4
5

4 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Sep 25, 2014 at 11:20 AM
Hi Abhishek643,

You can use the text delimiter, using "," as separator, to spread you data over columns.
Then select and copy your data. Now use PasteSpecial with the Transpose option checked to spread your data across rows.

Best regards,
Trowa
0
abhishek643
Posts
3
Registration date
Thursday September 25, 2014
Status
Member
Last seen
September 26, 2014

Sep 26, 2014 at 02:53 PM
Hi,

Thanks for the reply. currently i am manually doing it. in all the cells no of values are different so i have to first check how many values are there and insert new rows. And same process i have to follow for all the cells in workbook.

if there is any macro which can identify no values and as per the insert rows then manual work will be reduced.
0
abhishek643
Posts
3
Registration date
Thursday September 25, 2014
Status
Member
Last seen
September 26, 2014

Sep 26, 2014 at 03:34 PM
Hi,

i have got an macro which will covert cell value to into columns.
now i want macro for below
below are the values in columns.

1 2 3 4 5 6
2 3 4 5 6 7
i want outputs as below.
1
2
3
4
5
6
2
3
4
5
6
7


Thanks,
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Sep 29, 2014 at 12:01 PM
Hi Abhishek643,

See if the following code yields the desired results:
Sub RunMe()
Dim x, y, z As Long

x = 1
y = 2
z = 2

Do
    Cells(x, y).Cut
    Range("A" & z).Insert Shift:=xlDown
    y = y + 1
    z = z + 1
    If IsEmpty(Cells(x, y)) = True Then
        x = x + 1
        y = 2
        z = z + 1
    End If
Loop Until IsEmpty(Cells(x, y))

End Sub


Best regards,
Trowa
0