Convert values in 1 cell to rowsClosed

Question

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

Configuration: Windows 7 / Chrome 37.0.2062.120

TrowaD · Answer

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

abhishek643 · Answer

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.

abhishek643 · Answer

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,

TrowaD · Answer

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

Convert values in 1 cell to rows

4 replies

Subscribe To Our Newsletter!