asked on

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

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

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

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

Start Free Trial

Alex Campbell

ASKER

Exactly what I was looking for.

Berry Metzger

@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

Alex Campbell

ASKER

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.

Berry Metzger

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

Alex Campbell

ASKER

What question is that?

Berry Metzger

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

Alex Campbell

ASKER

Ah... all has been revealed. 8^)