+ Reply to Thread
Results 1 to 3 of 3

Changing multiple substrings within a string

  1. #1
    Registered User
    Join Date
    02-14-2012
    Location
    Rochester, NY
    MS-Off Ver
    Excel 2010
    Posts
    20

    Changing multiple substrings within a string

    Hello all!

    First post from a VBA dabbler/beginner incoming.

    I'm attempting to write a function that will look at a user-generated street address and "fix" it by:
    a) Correctly abbreviating everything that can be abbreviated,
    b) Removing unnecessary punctuation, and
    c) To a lesser extent, correcting common spelling errors.

    So "1234 East Rumson Raod" becomes "1234 E Rumson Rd".

    The function has to be able to handle multiple variations of the same theme (ie. "Street", "St.", "Str", "Str." all become "St"). That said, I'm not trying to catch everything right off the bat. Right now, I'm trying to use the following:

    Please Login or Register  to view this content.
    Unfortunately the code above returns nothing. I also just read that the Replace function only returns the string up to the end of the replaced portion, so even if it was returning something it probably wouldn't be correct.

    Can anyone tell me why that above returns nothing, and point me in the direction of a function that will return an entire string after part of it has been replaced?

    Thanks all!
    RA

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Changing multiple substrings within a string

    I got the following version to work:
    Please Login or Register  to view this content.
    edit: i finally understood your remark:
    I also just read that the Replace function only returns the string up to the end of the replaced portion, so even if it was returning something it probably wouldn't be correct.
    Last edited by WHER; 02-14-2012 at 12:59 PM.

  3. #3
    Registered User
    Join Date
    02-14-2012
    Location
    Rochester, NY
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Changing multiple substrings within a string

    Ah yes, I see what I did wrong. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1