Seperating Add1 Add2 City State and Zip

[HankK]

Hello all, I looking for a little help on seperating a single cell in Excel 2000. The cell contains add1,add2,city,state, & zip. The problem is that some records contain just add1, city, state, and zip with no add2.

419 500TH ST BOX 999 MAMARONECK NY 10543
1511 Helphand PL ATLANTIC CITY NJ 08401
BOX 999 GLENWOOD NJ 07418
APT Z 324 W Apple ST VINELAND NJ 08360

I have been trying to seperate them as
Column B Column C Column D Column E Coulmn F
add1 add2 city state zip
(if present)


Thanks
Hank

 

[JohnYingling]

Try SPLITting.

Dim arySplit() as String
Dim I as Long
if Len(strAddr) > 0 then
    arySPlit = Split(strAddr,",")
    if UBound(arySplit) < 4) then ' No addr 2 
        Redim arySplit(4)
        For I = Ubound(arySplit) to 2 Step -1 
            arySplit(I) = arySplit(I-1)
            arySplit(I-1) = &quot;&quot;
        Next
     strAddr1 = arySPlit(0)
     strAddr2 = arySplit(1)
     strCity  = arySplit(2)        
     strState  = arySplit(3)        
     strZip  = arySplit(4)
else
   ' no addr info
End if

http://www.VBCompare.com

http://www.VBSortGen.com

 

[logius]

Question on this: Will there always be a tab separating the groups of data? Or, more to the point, what are those gaps between the values? ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[MichaelRed]

unless you can give a (sensible and consistient) answer to loquis' question, this cannot be accomplished programatically without A LOT of suppoorting information.

John Yinglings approach will not work, because hte subfields are NOT comma delimited. Attempting to use another "character" would depend on THAT character being used as a seperator for each field. This cannot be the space character, as addr1, addr2, and city may be compound (e.g. include Space within the sub-field). The 'fields' do nnot have a constant (Character) width, so you cannot use a location to determine the start/end of fields.

It would appear -from the sample- that you could get the state and zip codes from the existing information. IF this is correct, you could use a zip code lookup to obtain the (primary) city for the zip code and (where this matched part of the address string) extract/isolate the city from the source string. This would still leavee the Addr1 and (possible) addr2 to be seperated by other processes (read manually).

The inclusion of the entire addrress in a single element (Cell?) without the formatting seems unusual (and relatively useless), so it would appear that your presentation either is lacking in the formatting information, or the users of this spreadsheet never intended to use this as a formal address parameter.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[HankK]

LOGIUS,

Yes, each field add1 add2 csz are always 30 bytes, populated or not. The data is just pushed back 30 if it's not populated. But the entire field will always have 90 bytes.


Hank Kirlin

 

[JohnYingling]

Dim arySplit(2) as String
Dim I as Long
Dim L as long
L = Len(strAddr)
if Len(strAddr) > 30 then
    arySplit(0) = Mid$(strAddr,1,30)
    I = 61 
    If L > 90 then 
        arySplit(1) = Mid$(strAaddr,61,30)         
        I = I + 91
    End if
    If L > I then arySplit(2) = Mid$(strAaddr,I,30)
End if

http://www.VBCompare.com

http://www.VBSortGen.com