+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : How do I search an address text string within a column and return the zip code in the

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    2

    How do I search an address text string within a column and return the zip code in the

    Hi,

    This is my first post, so I hope I am following protocol.

    I have 20,000 addresses, and each address is in one cell. I am trying to segregate the zip code from the address string and put it in the cell next to the address.

    I thought that I could do this easily using the text-to-column function on the data tab. This works, but it is cumbersome, for three reasons. First, the number of words and numbers in the addresses vary. Second, some cells are blank. Third, some of the addresses, particularly those in Texas, have street numbers that are five letters long, just like the zip codes are.

    I looked up a post from Johannes121 entitled “How to search text string within a column and return search value in adjacent column.” The solution used the LEN function to find the correct answer. I tried using this function, but I kept getting blank cells as my answer. I know that I am making a simple mistake. Does anyone have a solution?

    See example below. Column A shows the addresses that I have. Column B shows the zip codes that I want

    Column A
    7591 Yellow Tulip, Austin, TX 73259-2880
    25580 New Smith Rd, Apt. d25, Summerville, SC 23485
    Joe’s Car Wash, 3480 York Road, Towson, MD 21087
    23123 Gay Goodview Blvd, Apt # 318, Lubbock, Texas 79401

    Column B
    73259
    23845
    21087
    79401

    Thanks in advance.
    Last edited by marchsusan; 07-27-2011 at 01:44 PM. Reason: Davegugg solved my problem expertly!

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: How do I search an address text string within a column and return the zip code in

    Posting a sample workbook is much easier to work with than pasting text. That said, you should be able to copy down this formula:
    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Registered User
    Join Date
    07-27-2011
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How do I search an address text string within a column and return the zip code in

    Davegugg! you are a genius. thank you so much. you saved me tens of hours of mindless work! It works well. Now my big challenge is figuring out what the formula means.

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: How do I search an address text string within a column and return the zip code in

    It says: If the fifth character from the right is - (aka has the full 9 digit zip code), extract characters 6 - 10 from the right, otherwise, take the last five characters from the right.

+ 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