Split Text from Column to rows

Wonder if anyone can help me out with the VBA code. Following is the example of table that I would eventually have to split

Source
------
PART_ID DESCRIPTION TYPE
00001 CAPILLARY X,Y
00002 HEATER BLOCK X,Y,Z
00003 FRAME Y

Output needed
-------------

PART_ID DESCRIPTION TYPE
00001 CAPILLARY X
00001 CAPILLARY Y
00002 HEATER_BLOCK X
00002 HEATER_BLOCK Y
00002 HEATER_BLOCK Z
00003 FRAME Y

Thanking in advanced.. :D

Chitra

Vishesh's picture

In Description, the entry

In Description, the entry Heater block appears as two words in Source section. However, in Output needed section the two words are joined by "_". Its necessary for it to be joined with something in the source section as well for any macro/logic to be applied.

Nick's picture

I'll start u off - first, you

I'll start u off -
first, you need to do a text to columns.
- select col 1, the go: Data=> text to columns
choose delimited, and put ","

this will split the data up
then loop down the remaining data, and relicate the row if something exists to the right of column A..

Actually the data is made up

Actually the data is made up for 3 columns, so that means I would need to do the Data --> text to columns with column 3 right? and also does that mean I would need to relicate the row if something exist in column D onwards?

Nick's picture

ah.. if it's in col c, you're

ah.. if it's in col c, you're right..
Also, for this to work, it needs to be the last column

Thank u so much.. I will try

Thank u so much.. I will try and get that done :)