+ Reply to Thread
Results 1 to 5 of 5

Parsing Address/ZipCode

  1. #1
    Forum Contributor
    Join Date
    03-30-2008
    Posts
    121

    Parsing Address/ZipCode

    I am trying to parse zipcode into a separate field. A sample of
    the text is printed below.

    I am using the following statement, however, I am getting very
    inconsistent results....it is cutting off many of the numbers.

    =RIGHT(D3,FIND(" ",D3,1))

    BLOOMINGTON IN 47403-4738
    BLOOMINGTON IN 47401-9705
    WESTPORT IN 47283-9414
    INDIANAPOLIS IN 46234-5051

    What am I missing? Thanks.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Parsing Address/ZipCode

    What do you want to extract? =RIGHT(D3,10)?

  3. #3
    Forum Contributor
    Join Date
    03-30-2008
    Posts
    121

    Re: Parsing Address/ZipCode

    Thanks. That did the trick.

  4. #4
    Registered User
    Join Date
    06-28-2007
    Posts
    69

    Re: Parsing Address/ZipCode

    Here's a buch of different formulas. You must make sure that the city/state/zip cells (A2) do not have spaces after them. If so, use the "trim" function to remove.
    Attached Files Attached Files

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Parsing Address/ZipCode

    Just to explain your formula:

    =RIGHT(text,num_chars)

    So, from D3 you take 10 characterst from RIGHT part of the word.

    Your formula:

    =RIGHT(D3,FIND(" ",D3,1))

    Would have more sence for LEFT part of the word:

    =LEFT(text,num_chars)

    Where number of characters would be: FIND(" ",D3,1) means find first space in word (for BLOOMINGTON IN 47403-4738 would be on 12th position).

    That's the way hot to extract FIRST WORD IN A STRING before space.

    To be more precise you should subtract one character:

    =LEFT(D3,FIND(" ",D3,1)-1) because space is on 12th position (in upper example) and BLOOMINGTON has 11 characters

+ 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