VBA Extract Numbers And Insert Words

Hi All,

Hopefully someone will be able to help me with this.

I’m slowly teaching myself some VBA but it’s an uphill struggle at the moment!!!

I have attached a copy of the spreadsheet im working on, What I need to do is:

In column N insert the word ‘Class’ in front of the numbers in the column and ‘:’ after the number followed by a hard return, so N2 goes from this –

14 Precious metals and their allies and goods in precious metals or coated therewith, not included in other classes; jewellery (imitation jewellery of precious metals and stones); precious metals; cuff links, tie pins, horological and chronometric instruments.

To this:

Class 14:
Precious metals and their allies and goods in precious metals or coated therewith, not included in other classes; jewellery (imitation jewellery of precious metals and stones); precious metals; cuff links, tie pins, horological and chronometric instruments.

The way i was doing this is by using find and replace for all the various different numbers, most of the numbers seem to follow with 3 or 4 spaces so i was quite easily able to isolate them and change them all except that I couldn’t manage to get the hard return to fall in the fight place always (see further below)

It starts like: (Cell N94)

9 Swimming goggles; sunglasses; spectacles; eyewear; life-saving and teaching apparatus and instruments; swimmers and divers earplugs; nose clips; bathing floats; life buoys; life belts; breathing apparatus for underwater swimming. 18 Leather and imitations of leather, and goods made of these materials not included in other classes; animal skins, hides; trunks and travelling bags; umbrellas, parasols and walking sticks; bags; satchels; holdalls; wallets; purses; trunks; portfolios; attaché cases; briefcases; backpacks; gym bags; sports bags; tote bags; suitcases. 25 Clothing, footwear, headgear, swimwear.

I can get it to this by using the find and replace feature:

Class 9:
Swimming goggles; sunglasses; spectacles; eyewear; life-saving and teaching apparatus and instruments; swimmers and divers earplugs; nose clips; bathing floats; life buoys; life belts; breathing apparatus for underwater swimming. Class 18:
Leather and imitations of leather, and goods made of these materials not included in other classes; animal skins, hides; trunks and travelling bags; umbrellas, parasols and walking sticks; bags; satchels; holdalls; wallets; purses; trunks; portfolios; attaché cases; briefcases; backpacks; gym bags; sports bags; tote bags; suitcases. Class 25:
Clothing, footwear, headgear, swimwear.

But i need it like this:

Class 9:
Swimming goggles; sunglasses; spectacles; eyewear; life-saving and teaching apparatus and instruments; swimmers and divers earplugs; nose clips; bathing floats; life buoys; life belts; breathing apparatus for underwater swimming.
Class 18:
Leather and imitations of leather, and goods made of these materials not included in other classes; animal skins, hides; trunks and travelling bags; umbrellas, parasols and walking sticks; bags; satchels; holdalls; wallets; purses; trunks; portfolios; attaché cases; briefcases; backpacks; gym bags; sports bags; tote bags; suitcases.
Class 25:
Clothing, footwear, headgear, swimwear.

I have been going through the sheet manually to get it straight at the moment which is a pain.

There is also a slight problem in that some of the cells already contain the wording ‘Local Class #’ (see cell N1515), these need to stay as they are. (I’m not sure if this is a problem or not)

The next thing to do is i need to pull through the numbers from the Colum N into Columns I-M in numerical order (which they should already be in) ( some more columns need to be added in to accommodate all the numbers that are in column N, i haven’t added them in yet, 8 extra colums should do it). I was using ‘getnumber’ to extract the numbers but it just bunched them together, i was trying to get a space or a delimiter inserted so i could do text to columns’ and then pull them across but that’s where i got stuck!!

I need to apply this to quite a few sheets which is why I’m asking for help as they are all in the same format and need to have the same thing done to them!!!

I hope it all makes sense (ignore what is in column O as this is just me playing about)

Any help you can give me or pointing me in the right direction would be greatly appreciated.

Many thanks

Jamie

Oh, and im using Excel 2010