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


John Smith
My Street

When it should be

John Smith
11, My Street

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question