Selective Find and Replace in addresses

[Colin Hayes]

HI All

I have a lot of addresses which I'm trying to tidy via search and
replace in VBA.

Essentially , where there is a ", " (comma space) I want to replace this
with a carriage return. I use ^p to do this.

Unfortunately , this disrupts the address incorrectly when there is a
comma space following numbers.

Example ;

John Smith
11, My Street, Anytown

becomes

John Smith
11
My Street
Anytown

When it should be

John Smith
11, My Street
Anytown

I tried using advanced 'any character' formulas , but they incorrectly
replace the first letters of the words.

Can someone advise how to make the carriage retrurn active where the
comma space follows a letter , and not when it follows a number?

Grateful for any advice.

 

[Barry Schwarz]

If you find the comma space, select it, and then extend the selection
back one character, you can test the character for numeric or alpha
and then take the appropriate action.
[