Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-16-2012, 06:00 AM
Chayes Chayes is offline Selective Find and Replace in addresses Windows XP Selective Find and Replace in addresses Office 2003
Advanced Beginner
Selective Find and Replace in addresses
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default Selective Find and Replace in addresses

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.
Reply With Quote
  #2  
Old 06-16-2012, 03:04 PM
macropod's Avatar
macropod macropod is offline Selective Find and Replace in addresses Windows 7 64bit Selective Find and Replace in addresses Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use a wildcard Find/Replace, where:
Find = ([!0-9]),
Replace =\1^p
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-16-2012, 04:21 PM
Chayes Chayes is offline Selective Find and Replace in addresses Windows XP Selective Find and Replace in addresses Office 2003
Advanced Beginner
Selective Find and Replace in addresses
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi

Ok thanks for that - that's got it working fine.

It puts a carriage return for letters but not for numbers , which is perfect.

Many thanks.

Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selective Find and Replace in addresses Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM
Selective Find and Replace in addresses Is there a way to use "find/replace" to find italics words? slayda Word 3 09-14-2011 02:16 PM
Selective Find and Replace in addresses Help with find and replace or query and replace shabbaranks Excel 4 03-19-2011 08:38 AM
help with find and replace sjkiss Word 1 04-12-2010 08:08 AM
Find /Replace Help ilcaa Word 2 11-02-2009 08:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft