VBA to Insert Word, : and Hard Return

[jamieray]

Hi All,

In column N i need to insert the word ‘Class’ in front of the a all numbers (there are lots of different numbers) that are in that column and ‘:’ after the number followed by a hard return, so goes from this –

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.

To this: (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.

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

I have been going through the sheet manually to get it straight at the moment which is a pain as the sheet is 2000 rows and i need to to the same to other sheets.

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

Many thanks (im using Excel 2010)

Jamie

[arlu1201]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.