Previous Topic Next Topic

Convert values in 1 cell to rows

[Closed]
Report
Posts
3
Registration date
Thursday September 25, 2014
Status
Member
Last seen
September 26, 2014
 -
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
 -
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

Answer 1 / 4
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
 482
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
Answer 2 / 4
Posts
3
Registration date
Thursday September 25, 2014
Status
Member
Last seen
September 26, 2014
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.
Answer 3 / 4
Posts
3
Registration date
Thursday September 25, 2014
Status
Member
Last seen
September 26, 2014
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,
Answer 4 / 4
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
 482
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
Ask a question