Can I separate these text strings by number/letter blocks to produce new strings...?

[EgoPlacebo]

Not sure if this should be in this thread or the programming one…
I'm relatively inexperienced in functions, macros etc. But I can usually figure things out with some extensive googling.. Unfortunately this one has me stumped.
If anyone can help that would be AWESOME!

I have a keyword field in which I need to have multiple versions of each "keyword".
My starting point is something like this:
HL720, HL730, HL760, HL770, 2660, 2750, 3550, 3650, 3750, 8000p, 8200p, 4340, 4350, 4450, 4550, 4600, 4650, 6550MC, 6650, 7300DX, 7550ML, 7650MC, 7750MC, 9000, 9500, 1040, 1050, L1060, 1070, P2000, FAX2850, FAX8070P, MFC4800, MFC9160, MFC9180

But I need to cover all the different ways people might type these in a search field.
e.g. for HL720 likely variations are: HL720, HL-720, HL 720

So, I could cover HL720, HL730, HL760 and HL770 by including HL, 720, 730, 760, 770, HL-720, HL720, HL730, HL760, HL770, HL-720, HL-730, HL-760, HL-770


Does anyone have any idea how I could separate the numbers from the letters and end up with these variations, keeping in mind that:
• there are 1645 of these entries in a single column to clean up?
• Each of these comma separated lists are in ONE cell.

If I can separate each string (e.g. HL720) by letters and numbers with a dash (HL-720) that would be a good start, in my searches I've seen many ways of doing things like this when all the strings follow the same format (example 2 numbers followed by 3 letters) but I have been unable to find one for this situation where the quantities and placements of the letters and numbers vary.

Then I'd need one separated copy of each of the letter blocks and number blocks, and finally, concatenate the whole list with commas to separate.
The lists are also tied to product codes in another column, this needs to be maintained.

I spent the better part of a day trying to find a way to do this without having to manually go through every single field but I haven't even come close!

[SDCh]

Assume text on column A2, Formula on B2:

Separate text into rows (drag down for copy):

Please Login or Register  to view this content.

Separate text into columns (drag across for copy):

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Required data can be extracted using macro. Pl see attached file with macro. Data is extracted from A column to B column.