Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to convert Excel list with commas to Excel list without commas?

Posted on 2014-09-28
Medium Priority
148 Views
I have a list in an Excel file where each row has a column A and a column B.

Here are some example rows in the tab Input from the attached file:
0x0      Hair, Football, Cats, Bees
1x01      Cats, Michael Jackson, Beards, Coffee
1x02      Human Body, Coca-Cola, Morris Dancing, Carrots
1x03      Chickens, London Underground, Queen Elizabeth I, Ancient Egyptians
1x04      George W. Bush, Women, Ants, Olympic Games

A new tab should be created as Output with the data converted to:
0x0      Hair
0x0      Football
0x0      Cats
0x0      Bees
1x01      Cats
1x01      Michael Jackson
1x01      Beards
1x01      Coffee

CreateList.xlsm
0
Question by:Alex Campbell
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40348898
The following VBA subroutine will parse and transpose your data into two columns as requested:
``````Option Explicit
Sub Parse_and_Transpose()
Dim rng As Range
Dim cl As Object
Dim arrItems As Variant
Dim strCat As String
Dim x As Integer
Dim r As Long

'Remove any existing Output sheet first
On Error Resume Next
Sheets("Output").Delete

ActiveSheet.Name = "Output"
Sheets("Input").Select

Set rng = Range("A1", Range("A1").End(xlDown))
r = 1
For Each cl In rng
strCat = cl.Value
arrItems = Split(cl.Offset(0, 1).Value, ", ")
For x = 0 To UBound(arrItems)
Sheets("Output").Cells(r, 1).Value = strCat
Sheets("Output").Cells(r, 2).Value = arrItems(x)
r = r + 1
Next x
arrItems = Null
Next cl
MsgBox "Done."
End Sub
``````

This presumes there are no blank cells in column A.

Example macro-enabled file is attached.

Regards,
-Glenn
EE-CreateList.xlsm
0

LVL 1

Author Closing Comment

ID: 40349108
Exactly what I was looking for.
0

Expert Comment

ID: 40353638
@Alex972
I believe your formulas in F17:F22 are incorrect. The first two formulas are identical as follows...
F17: =D17*VLOOKUP(E17,\$A\$5:\$D\$13,3,TRUE)
F18: =D17*VLOOKUP(E17,\$A\$5:\$D\$13,3,TRUE)  where it should be: =D17*VLOOKUP(E18,\$A\$5:\$D\$13,3,TRUE)
...and so the one-row reference mistake continues for the remainder of the range.
Berry
0

LVL 1

Author Comment

ID: 40360471
Thanks for the notificatios, but I not sure what file this is in regard to. The data file I uploaded doesn't have any formulas.
0

Expert Comment

ID: 40360742
It is in reference to your Commission-split Comparison-EDIT-UPDATE.xlsx file that Glenn Ray assisted you with.
0

LVL 1

Author Comment

ID: 40360821
What question is that?
0

Expert Comment

ID: 40361173
Ahaaa! it was not your question, it was by  DEROUCHE solved on 9/30/14.  Don't know how your name got referenced.
Berry
0

LVL 1

Author Comment

ID: 40361207
Ah... all has been revealed. 8^)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month5 days, 22 hours left to enroll