Excel 2007 : 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.