Selective Find and Replace in addresses

Discussion in 'Word VBA Beginners' started by Colin Hayes, Jun 16, 2012.

  1. Colin Hayes

    Colin Hayes Guest

    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.
     
    Colin Hayes, Jun 16, 2012
    #1
    1. Advertisements

  2. 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.
    [

    On Sat, 16 Jun 2012 23:05:10 +0100, Colin Hayes
    <> wrote:

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


    --
    Remove del for email
     
    Barry Schwarz, Jun 17, 2012
    #2
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Tina

    find and replace exact phrase

    Tina, Aug 11, 2003, in forum: Word VBA Beginners
    Replies:
    1
    Views:
    161
    Harold Kless[MSFT}
    Aug 12, 2003
  2. Jon Petitta

    Access db, find and replace in word

    Jon Petitta, Sep 18, 2003, in forum: Word VBA Beginners
    Replies:
    1
    Views:
    174
    Doug Robbins - Word MVP
    Sep 19, 2003
  3. Jay Freedman
    Replies:
    1
    Views:
    452
    Klaus Linke
    Sep 30, 2003
  4. Connie

    Find and Insert not Replace

    Connie, Dec 17, 2003, in forum: Word VBA Beginners
    Replies:
    5
    Views:
    126
    Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT
    Dec 23, 2003
  5. Mark Rowland

    Limit find and replace

    Mark Rowland, Dec 21, 2003, in forum: Word VBA Beginners
    Replies:
    2
    Views:
    111
    Jay Freedman
    Dec 21, 2003
  6. Robert

    Using find and replace to delete paragraph

    Robert, Jan 3, 2004, in forum: Word VBA Beginners
    Replies:
    2
    Views:
    138
    Robert
    Jan 3, 2004
  7. Quetzalcoatl

    VBA : (selective) deletion of hypertext links

    Quetzalcoatl, Aug 27, 2004, in forum: Word VBA Beginners
    Replies:
    12
    Views:
    142
    Jezebel
    Aug 30, 2004
  8. Kim

    Find and Replace with Do While and Loops

    Kim, Jul 26, 2007, in forum: Word VBA Beginners
    Replies:
    4
    Views:
    162
Loading...