Sort data into multiple rows

[DAC]

Hi,
First post so thanks in advance for any help on this.

I'm trying to merge 2 spreadsheets togeather but befor I can do this I need to resolve the following issue - my Excel worksheet has 2 columns with data that looks like this
Value1 X1,X2,X5
I need it to change to this
Value1 X1
Value1 X2
Value1 X5

DAC

[JBeaucaire]

That first Value1 is all in one cell? Can you post several real examples of the data? Formulas can be used to parse out data around commas, but it's best to see some real data.

[DAC]

That first Value1 is all in one cell? Can you post several real examples of the data? Formulas can be used to parse out data around commas, but it's best to see some real data.

Yes Value1 is in one cell

This is an actual example
CCB012G C06, C08, C15, C20, C64, C95
DSX003G D08, D18, D19, D20, D22, D24, D28

It is a bill of materials for an electronic assembly with the part number in one cell & the reference designators in the other cell, these are separated by commas, the reference designators will typically contain either 3 or 4 characters.

Thank you.

[JBeaucaire]

This seemed hard at first, then I realized Excel has a transposition function designed for this.

First, convert you data into separate columns. Highlight the cells that have all the data in one cell, then use TEXT TO COLUMNS to split it. Choose delimited, choose comma and space delimiters. Now all your codes are separated. You can actually do this for your whole data set in one fell swoop.

Now highlight the whole data set including the part # and copy it, put your cursor somewhere where there is a lot of open space, then choose Edit > Paste Special and tick the Transpose box.

Voila.

[DAC]

Thank you very much

[JBeaucaire]

Be sure to EDIT your original post and mark the PREFIX box as [SOLVED]