Split cell values based on "," and "-" and format as shown below

**Roop** · 03-21-2012, 02:20 PM

Hi Guys,
My raw data looks like this:-

Package
1. PAxx
2. ABxx
3.DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf
4.WBxx
5.NABpc,cu
6.FOXdi,cu
7.GPCpc,cu
8.GGxd-HDxd
9.VFHpb

And i am trying to format it as show:-

Package Item
1. PAxx Paxx
2. ABxx Abxx
3. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf DSSxd
4. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf DSSxf
5. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf Ygxd
6. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf YGxf
7. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf RAKxd
8. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf RAKxf
9. DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf JJxd
10.DSSxd,xf-YGxd,xf-RAKxd,xf-JJxd,xf JJxf
11.WBxx WBxx
12.NABpc,cu NABpc
13.NABpc,cu NABcu
14.FOXdi,cu FOXdi
15.FOXdi,cu FOXcu
16.GPCpc,cu GPCpc
17.GPCpc,cu GPCcu
18.GGxd-HDxd GGxd
19.GGxd-HDxd HDxd
20.VFHpb VFHpb

I am really sorry, i dont have permission to post a "Test file" . I will appreciat any help.

Thanks,

Roop

**dilipandey** · 03-21-2012, 02:23 PM

Hi Roop,

Welcome to the forum.

Try exploring the Text To Column Feature of Excel.. it will help you. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**MaczaQ** · 03-21-2012, 04:17 PM

for each line you could try to use following code
copy it into module, and let say you have data in column A - so in B1 type "=explode_and_join(A1)"

Please Login or Register  to view this content.

now I see that above code didn't solve your problem in all condition - I will think about it.

**Roop** · 03-21-2012, 08:42 PM

Originally Posted by dilipandey

Hi Roop,

Welcome to the forum.

Try exploring the Text To Column Feature of Excel.. it will help you. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi,
Thanks. I will try that