Replace SPACE with COMMA Unless preceded by a number or specific text

[bluemanuk1]

Hi,

I am looking for some help on a VBA to replace a space with a comma unless it is preceded by a number.

So on the two following examples...

47 Fairmile Gardens Longford Gloucester Gloucestershire GL2 9EA,
354 Finchley Rd Greater London NW3 7AJ

The result would like like this below

47 Fairmile Gardens,Longford,Gloucester,Gloucestershire,GL2 9EA ( SPACE not replaced between 47 & Fairmile because it is preceded by a NUMBER, in this case 47, also the SPACE between Fairmile and Gardens not replaced because it is next to the words GARDENS.)
354 Finchley Rd,Greater London,NW3 7AJ ( SPACE NOT REPLACED BETWEEN 354 & FINCHLEY BECAUSE IT IS PRECEDED BY A NUMBER, IN THIS 354, also the SPACE between Finchley and Rd is not replaced because it is next to Rd. Same concept with Greater London. )

Any additional words that trigger the ignoring of the replace code need to be able to be added in the code manually.

Thank you in advance.

[ubludkisranye]

Here's a simple way of doing it after selection the data in the sheet.

Please Login or Register  to view this content.

[bluemanuk1]

Thank you so much for the reply. I'll test it out and let you know how I get on.

[bluemanuk1]

This worked perfectly first time, thank you once again.