Reformatting comma delimited text with VBA

[MichaelT]

I have a spreadsheet that has some cells with comma delimited text (see below) in it that I am trying to break apart into a form that will be more suitable for Access entry. I have tried to do this with the Text to Column feature but it requires a lot of cuttting and pasting because there are so many rows. Is there a way to do this with VBA?

Thank you,
Michael Armstrong

Original form:
Text1 Item1 OtherItems
Text2 Item2, Item3, Item4 OtherItems
Text3 Item5, Item6 Other Items

convert to:
Text2 Item2 OtherItems OtherItems
Text2 Item3 OtherItems OtherItems
Text2 Item4 OtherItems OtherItems
Text3 Item5 OtherItems OtherItems
Text3 Item6 OtherItems OtherItems

 

[larai]

AS long as there is a pattern or a distinct method to differentiate between which items go into certain columns/rows, then yes.

Is this a one-time operation or will you do the same thing on a routine basis?

It might be more time to code something if your conversion is really complicated then to do it manually.

From the details mentioned, I cannot tell how many items you have or how the columns repeat as you have a different number of elements in each. Is item1 not used at all? Is it deleted?

Lil

 

[MichaelT]

Reply about reformatting:
1. This operation will be done repeatedly.
2. Any row with more than one item will have to be separated, so the text1 will not have to be done.
3. Each row may have different numbers of items that are comma-delimited.
Thanks. Michael

 

[larai]

I'd need to understand the difference between Item2, Item3 and what you mean by Otheritems?

Is it just something like:
Text1,data
Text2,data,data,data,data,data,data
Text3,data,data,data

email me @ larai@saturnee.com if you want to continue this offline. Its taking a long time for me to find threads I've responded to.