Selective Find and Replace in addresses

C

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.
 
B

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

Top