Separating multiple paragraphs of data from one cell in excel

[eagiordano]

Hello All.

I am looking to separate the following data into a certain format in excel. Currently the data is in one cell separated in paragraphs with titles. See below

---------

Case International IH
580, 1594, 1694, MXM120, MXM130, MXM140, MXM155, MXM175, MXM190

County
654, 754, 764, 774, 944, 964, 974, 1164, 1164TW, 1184TW

David Brown
1394, 1410, 1412, 1494, 1594, 1690, 1694

Fiat
M100, M115, M135, M160

Fordson
Major, Power Major, Super Major

Ford New Holland
230A, 231, 233, 234, 250C, 260C, 333, 334, 335, 345, 420, 445, 450, 455, 515, 530A, 531, 532, 535, 545, 550, 555, 575D, 650, 655, 675D, 750, 755, 2000, 2100, 2110, 2120, 2150, 2300, 2310, 2600, 2610, 2810, 2910, 3000, 3055, 3100, 3110, 3120, 3150, 3230, 3300, 3310, 3330, 3400, 3430, 3500, 3550, 3600, 3610, 3900, 4000, 4100, 4110, 4140, 4200, 4330, 4340, 4400, 4410, 4500, 4600, 4630, 4830, 5000, 5030, 5100, 5110, 5200, 5340, 5500, 5550, 5600, 5610, 5610S, 5640, 5700, 5900, 6410, 6500, 6600, 6610, 6610S, 6640, 6700, 6710, 6810, 6810S, 7000, 7100, 7200, 7410, 7500, 7600, 7610, 7610S, 7630, 7700, 7710, 7740, 7810, 7810S, 7840, 7910, 8000, 8030, 8160, 8200, 8210, 8240, 8260, 8340, 8360, 8530, 8560, 8600, 8630, 8700, 8730, 8830, 9000, 9200, 9600, 9700, TB100, TB120, TM115, TM120, TM125, TM130, TM135, TM140, TM150, TM155, TM165, TM165 Brazil, TM175, TM190, TS80, TS90, TS100, TS110, TS115, TW5, TW10, TW15, TW20, TW25, TW30, TW35

---------

The data listed above obviously changes from cell to cell but the format of having the title (Make) and data (Model) below is the same. All the models are separated by commas. I need to convert it into the following format in two columns. Each Model on individual rows with the Make listed beside it.

Thank you in advance for your help.

Regards,
eagiordano

 

[eagiordano]

Just add a space after the comma on this line as shown

               Sp = Split(Ar(i).Columns(j), ", ")

Perfect! Thank you very much.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JLGWhiz]

Hi @JLGWhiz the only issue with this is the time it would take to arrange the data into the format above. There are also multiple lines of data with multiple reference numbers. I think @Fluff has offered a solution which can use the format the data is originally in. But thank you for your help - it is greatly appreciated. Regards, eagiordano

Yes, as @Fluff has pointed out, with the proper code, it can be done with less data manipulation. I was just trying to get it to where I could understand what needed to be done.
regards, JLG