+ Reply to Thread
Results 1 to 13 of 13

Parse, Clean and Format Address Data from Text String

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Parse, Clean and Format Address Data from Text String

    Hi All—

    I am working with data which is obtained from multiple sources, and thus inconsistent in the way addresses are entered. I have formulas based on location criteria and they need to be uniformly formatted. The basic format needs to be as follows:

    10 W53 St
    565 5 Av

    Some users type out "Street" or "Avenue," some use "Ave." or "St.," etc.

    I have pieced together a basic macro which handles it fairly well, but it is in 3 parts and I often have to run each step twice in order for it to be thorough. My two main issues are:
    1. I would like to be able to combine the 3 parts into 1.
    2. Part 3 occasionally gets caught in a loop of some sort and Excel hangs for a while.
    3. I would like to be able to run the macro on "selected cells" rather than only on Column A. (The code was copied from a macro I found online and am not sure how to modify it so that it's not limited to the one column.


    Following is my code and attached is a spreadsheet with various ways I may receive the data.


    Please Login or Register  to view this content.



    Thanks in advance,
    Attached Files Attached Files

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Parse, Clean and Format Address Data from Text String

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    Perfect-- Thank you so much!

    I just had to add the
    Please Login or Register  to view this content.
    line after "For each cell in selection" since the replace is case-sensitive. Which is fine since I wanted it formatted in TitleCase.

    Just curious though, is there a way to run it without changing case across the board? Anything that is orignally in All Caps is not affected by the macro without doing so.

  4. #4
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    I also occasionally am getting Run-time error 1004: Unable to get the Substitute property of the WorkSheetFunction Class on
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Parse, Clean and Format Address Data from Text String

    Different method. All in one.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    Thanks- this works well except for the following:
    • Some instances of Ave are not changed to Av (where the address is the only info in the cell)
    • East/West leaves a space after. (i.e., should be 200 E66 St rather than 200 E 66 St)
    • In the cells where the text is "20379449-manhattan 451lexington Av" it isn't separating the 451 from (and capitalizing) Lexington

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Parse, Clean and Format Address Data from Text String

    Can you upload a small sample with your desired result?
    So that I can find how you want it.

  8. #8
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    Sure. Here you go. In "orig_v_correct.xlsm" Column A is how I receive data. Column C is how it should read. There are a few tricky situations like West End Av (not to become W End Av) and Center Street to be Center St (not Ctr St) where Rockefeller Center should read Rockefeller Ctr

    I really appreciate your help!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    orig_v_correct_2.xlsm is a bit easier to read
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Parse, Clean and Format Address Data from Text String

    Run the code on "Test" sheet to see the result against your result.

    Some of them are not Exactly the same. Capital letters vs Proper case.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    This is nearly perfect! Just a couple of things I realized (which could be accomplished with a search/replace too):
    Please Login or Register  to view this content.

    I realize you did it to compare but please modify so it will run in any column, correcting the selected text rather than create a corrected instance into a different one.

    Thank you so much!!
    Last edited by jerrydiaz; 12-04-2013 at 10:21 AM.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Parse, Clean and Format Address Data from Text String

    1)
    Please Login or Register  to view this content.
    2)
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Parse, Clean and Format Address Data from Text String

    Oh ok cool. So is this ok?



    Please Login or Register  to view this content.
    Last edited by jerrydiaz; 12-04-2013 at 12:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] text formula to parse a cell, clean it, and dump into a fixed CELL RANGE - NO MACROS
    By James C in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-04-2013, 08:42 PM
  2. Text string clean up formula needed
    By ZimmJJ in forum Excel General
    Replies: 5
    Last Post: 07-01-2012, 09:16 PM
  3. Parse Email address from middle of a string
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-23-2011, 09:17 PM
  4. How To Parse Specific text from String Data
    By zaidan in forum Excel General
    Replies: 2
    Last Post: 04-08-2011, 04:34 AM
  5. Find and parse email address from text string
    By jchamber00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2009, 11:33 AM

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