• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SPLIT DATA FROM A CELL TO DIFFERENT ROWS

Dear All,

Greetings!!

I have a data like this



Raw Data

C:\Users\PS003594\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png


Desired Result

C:\Users\PS003594\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png




File is attached for your reference.



Regards

Neeraj Kumar Agarwal
 

Attachments

  • Book1.xlsx
    8.9 KB · Views: 12
Hi Neeraj,

As far as my experience with Excel , there are 2 ways to achieve this

The first and easy way is to use Text to Columns. Select the comma separated cell and then click on Text to Columns. once the data is split into columns use Copy/ Paste Special values -> Transpose to get this into row format

Second way is to write a VBA function that achieves this.

Thanks,
Ramesh Kumar.P
 
Hi:

Since I was working on it.

Code:
Sub SplitData()

Application.DisplayAlerts = False

Dim str As Variant
Dim i As Long, j As Long, cnt As Long

i = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
cnt = 2

For j = 2 To i
    str = Split(Range("B" & j).Value, ",")
    Range("D" & cnt).Resize(UBound(str) - LBound(str) + 1).Value = Application.Transpose(str)
    Range("C" & cnt).Resize(UBound(str) - LBound(str) + 1).Value = Range("A" & j)
    cnt = Sheet1.Cells(Rows.Count, "D").End(xlUp).Row + 1
Next

Application.DisplayAlerts = True

End Sub

Thanks
 

Attachments

  • Book1.xlsm
    16.8 KB · Views: 4
Dear All,

Thanks for your support but I am looking for a non VBA Solution.
Ramesh Sir, solution suggested by you is no doubt wonderful but may not be fruitful when we have a large set of data.

May I request for any non vba solution??

Regards
Neeraj Kumar Agarwal
 
Hi:

Considering that you have huge amount of data and your data is not in a user friendly manner. I would suggest you to go for a VBA solution, any formula based solution will be really tricky and will increase the size of your file considerably.

Thanks
 
Back
Top