asked on

Separate content into different cells in Microsoft Excel

Hi

In my Excel sheet, one cell contains data like aa, bb, cc, dd, adfd, hls, sfa, ........ I need to separate each content separated by comma into different cells. please see example below.

Column A1 =aa
Column A2=bb
Column A3=cc
Column A4=dd
Column A5= adfd

I am looking for VBA script to do this task.

Thank you
A

Subodh Tiwari (Neeraj)

You may try something like this....

Sub SplitCellContent()
Dim wsData  As Worksheet
Dim wsOut   As Worksheet
Dim Rng     As Range
Dim arr     As Variant


'Assuming the data is in the worksheet called "Sheet1"
Set wsData = Worksheets("Sheet1")


'Assuming the cell with comma separated values is A1
Set Rng = wsData.Range("A1")


On Error Resume Next
Set wsOut = Worksheets("Output") 'The output will be placed on a worksheet called Output
wsOut.Cells.Clear
On Error GoTo 0


If wsOut Is Nothing Then
    Set wsOut = Worksheets.Add(after:=wsData)
    wsOut.Name = "Output"
End If


arr = Split(Rng.Value, ", ")


wsOut.Range("A1").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)
End Sub

Open in new window

In the attached, click the button called "Split Cell Content" to run the code to get the desired output on Output Sheet.

SplitCellContent.xlsm

Asatoma Sadgamaya

ASKER

Hi Subodh,

I have tested, but your code just replicates Sheet1 on Output

Sorry, on my question, content has less number of characters. I tried below but code has failed to separate it.
eg. Column A1= aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb,dddddddddddddddddddddd,cccccccccccccccccccccccccccc

Thank you
A

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Asatoma Sadgamaya

ASKER

Thank you for your help on this Subodh

Subodh Tiwari (Neeraj)

You're welcome Asatoma! Glad I could help.

Yolanda Cuesta Altieri

Hi In my opinion it would be better to use Power Query.
I recommend you to begin studying Power BI
Regards,
Yolanda

Subodh Tiwari (Neeraj)

As Yolanda suggested, you may also use Power Query to split the cell content by a delimiter into rows and it's one step query.

Refer to the attached with the Power Query Solution. If data changes in the cell A2 on Sheet1, you just need to click inside the table on Sheet2 and choose Refresh and the table would be updated.

SplitCellContent.xlsx