Home » Forums » Open Source Learning Center » Question and Answer

Split Text from Column to rows

Submitted by chitrakala on 24 February, 2012 - 13:21

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

‹ Divide one excel workbook to many How to share rows between excel sheets? ›
»
Vishesh's picture

In Description, the entry

Submitted by Vishesh on 25 February, 2012 - 09:57.

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

Submitted by Nick on 24 February, 2012 - 13:38.

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

Submitted by chitrakala on 24 February, 2012 - 14:09.

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

Submitted by Nick on 24 February, 2012 - 14:55.

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

Submitted by chitrakala on 24 February, 2012 - 13:51.

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

»