Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separating Data (VBA/Excel/97)

    Is there a VBA routine that will separate the following address into five separate Excel columns? The data contains the street number, name, city, state, and zip. A sample of the data file is included. Thanks.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Separating Data (VBA/Excel/97)

    Ouch that looks problematic. It is a real shame there are no consistent delimiters in your sample.

    I would approach this problem by copying into Word and then using a series of Wildcard search and replaces to put some text delimiters in. Excel has the useful Data > Text to Columns method that would be great if you could get some text delimiters in there somehow.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Data (VBA/Excel/97)

    I could easily write code that would give you the number, state, and zip code, but the street name and city have no way of identifying where the break is. If you can go through manually and insert some delimiter between those two, I can write some code that will split it up.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Good news & Bad

    Thanks. I will try and get hold of a newer version of Excel. (I work for the gov).

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Good news & Bad

    If you can get a single text list of California Cities, I can try to complete the code; I Googled around but couldn't find a list I could easily import. I could even run the whole works for you if you would be comfortable with sending me the raw data, understanding that privacy issues may prohibit. PM me if you can get a City list. (You don't have any info in your Profile, so I was curious; Federal or State Govt., if State, which?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Good news & Bad

    Yea, I don't think I could send you the whole file because of privacy issues, but thanks for the offer. Federal government. I work for the Morale, Welfare and Recreation division of the Navy (civilian, non-appropriated fund-NAF-not GS).

  7. #7
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Good news & Bad

    No need to upgrade Excel if you just need those string functions:
    http://www.freevbcode.com/ShowCode.Asp?ID=17

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Good news & Bad

    Thanks to Andrew Savikas for pointing to those functions, this should run in Excel 97. (Slight tweaking, and renaming of the functions so I could test on XL 2003.) Post back if not. This version solves some problems, such as replacing the wrong target, and should run faster since it doesn't write to the source cells. It still doesn't get Address2 separated, but you can do that manually by inserting a comma-&-space between Address2 and City as I did here on the 5th source data row.

    HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Good news & Bad

    Okay, a bit over my head. Well, quite a bit over my head to be honest. What does "Automation type not supported in Visual Basic" imply? This is the error I got when I tried to run the Split et. al. functions.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Good news & Bad

    <P ID="edit" class=small>(Edited by JohnBF on 08-Jun-04 08:07. Attachment removed as subsequent version is improved.)</P>First the bad news; I wrote the attached in VB6 using Replace() and Split() functions, which aren't available in Excel 97. I won't have time to rewrite it; perhaps another Lounger can help.

    Except for that major stumbling block, the attached code gets you everything but the "address2" such as apartment number, by assuming that the correct US abbreviation has been used for street (see list on the dedicated sheet in the workbook). As you'll see from the unused Dims, I was looking to get a list of California cities and place a comma in front of them to separate out the "Street2" portion of the address, but can't find a single CA City listing (and the code wouldn't be too speedy).

    I hope someone can make more of this.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Good news & Bad

    Office 97 used VBA 5.x, which did not contain the extremely useful Split() method. Office 2000 and higher used VBA 6.x, which made that method, among others, available. So for Office 97, people used to code Split() the hard way, using string functions to look for the delimiters within the bigger string, and store the pieces in an array. Same result, but more coding.

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Parsing Address

    I attempted to do this using formulas (no VBA) and a list of cities in state of California (list extracted (indirectly) from Zip code table as published by USPS) - see attached (zipped) Excel file. Without lookup table for the cities, I don't see any good way to "automatically" parse Address & City fields whether using workbook formulas or VBA, or both. Assuming the addresses are consistently formatted as in the sample file, formulas for parsing the other fields is relatively simple. Example (where cell A2 contains the input address):

    Street: =LEFT(A2,FIND(" ",A2,1)-1)

    State: =MID(A2,LEN(A2)-11,2)

    Zip: =RIGHT(A2,9)

    To extract Address & City fields, resorted to "intermediate" columns (Columns B thru G in attached) to tokenize the address text, then used following formulas:

    Address: =TRIM(LEFT(B2,LEN(B2)-LEN(J2)))

    (B2 is first "intermediate" column, J2 is City column.)

    City: =IF(ISNA(MATCH(C2,CityTable,0))=FALSE,C2, IF(ISNA(MATCH(D2,CityTable,0))=FALSE,D2,IF(ISNA(MA TCH(E2,CityTable,0))=FALSE,E2,IF(ISNA(MATCH(F2,Cit yTable,0))=FALSE,F2,IF(ISNA(MATCH(G2,CityTable,0)) =FALSE,G2)))))

    This ugly formula evalates each of the intermediate columns (excluding first) until match found in the "CityTable" lookup table (see 2nd worksheet). The number of intermediate columns was based on maximum number of tokens (words) likely to be found in the Address/City portion of address. Maybe someone knows a better or simpler way to derive the intermediate values without the extra columns (which can be hidden in actual use). If nothing else maybe the list of cities will be useful (data is current as of June 2004).

    HTH

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Good news & Bad

    I had the same problem when I tested briefly on my old Excel 97 installation, but due to other commitments didn't have time to resolve the problem. I'm guessing that our Excel 97 environment doesn't permit the passing of the 'Optional bCompare as VbCompareMethod = vbBinaryCompare' argument in the function.

    I have attached a version that now parses out Address2 fileds by locating the city names, which I obtained from WikiPedia. (Don't know how I missed in my Google it yesterday.)

    See if the attached runs; if not, look for someone with Excel 2000 or higher.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Good news & Bad

    Thanks to everyone's input. The program works well enough (I located a copy of Excel 2000). I forgot to include sample address from different states and POBoxes, but I'm going to try and work those issues out myself.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Good news & Bad

    The attached text file rewrite fro Excel 2000 or higher should handle multiple US States and should be yet faster, BUT you'll need to create a list of every City in every State, each with it's own range name in the form "STCityList" where 'ST' is the corresponding USPS State abbreviation, on a worksheet called "Cities". (With multiple state lists the code should run faster than with a single national list.) Ouch, a lot of work!

    I didn't think about PO Box addresses ... hmm, no wonder this work pays well! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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