How do I extract a 5 digit zip code from a column that may have a random number of text?

Hi all,

I have a spread sheet with a column that includes a random amount of text like so:

-----------------------


Contact Name:


Contact Email:


Contact Phone:

I am interested in 1234NE 12Tth, Williston, FL 32696.


Search CriteriaCity :
State :
ZIP : 0
MinLotSize : 0
MaxLotSize : 0
MinYearBuilt : 0
MaxYearBuilt : 0
PropertyTypes : CondoApartment, LotsLand, Manufactured, MultiFamily, SingleFamily

-------------------

What I want to do: Extract only the zip code from this column.
My problem: I cannot find a formula to complete this task because every cell is different. Some may display the above posted or some may have double the text, so I am not sure how to pinpoint the formula to do what I want it to do.

The words "I am interested in" appear in a majority of cells in the column, followed by the respective state, if that helps.

I am brand new with macros, so I am not entirely sure how to run this.

I selected macros on the upper left, then created the macro by copying and pasting your code. Since the zip codes are in column D, I changed A to D instead. After I save the macro file, I am not too sure how to run and test it.

I would ultimately want the zip codes inputted to a brand new column on the right.

Thanks for the tip.

So attached is a sample workbook of what I'll be working on. As you can see under the "notes" column, there is a body of text with the zip code. I want to be able to extract only the zip code shown to the row over to the right (column E)

Thanks to everybody for the help so far.

As both examples with zip codes relate to Florida, you could put this in E2:

=IFERROR(MID(D2,FIND("FL",D2)+3,5),"")

then copy down.

Hope this helps.

Pete

Thank you for the help, I followed the instructions provided but it did not fill only the zip code in column E. Instead, it seems like it copied and pasted some text from column D to E.

Hey, this worked! Thank you so much. So for different states, i would just have to replace "FL" with the correct one, right? Since the zip codes may be random, I would still need a useful macro to execute all zip codes, no matter the location.

There were only two examples (from three records) to work from. Both of them had FL followed by a space followed by the 5 digits that you wanted to extract. If you wanted something more generalised, you will need to submit another attachment which gives some more examples of the data that you have.

Pete

Hi Pete,

Thanks for the reply. The rest of the data in the D column is in the same format with random, different states, as well as some extraneous notes that contain no numerical value.

For cells D3 and on, would I just have to replace the location with this and the state?

You would put the amended formula in cell E2, and then just copy it down as far as necessary.

Pete

Here's an UDF solution.
Use in cell like
=GetUSZipCode(D2)

Please Login or Register  to view this content.

Pete,

Thanks for all the help. When I copied and pasted that formula on my end, it produced an error, stating I would need to check my formula again.

I believe we are on the right track to solution, so thanks again for all of your help.

Sorry, I just spotted that I had left two = signs in the middle of the formula. I shall edit the previous post to show what it should be.

Pete

Much appreciated Pete. I'll look out for it.

That would make the formula very lengthy, and difficult to maintain. It would be better if you listed the state letters in a column (suppose this occupies cells I1 to I50), and then in E2 you could use this formula:

=IFERROR(MID(D2,FIND(INDEX($I$1:$I$50,SUMPRODUCT(--(ISNUMBER(FIND(" "&$I$1:$I$50&" ",D2))),ROW($I$1:$I$50))),D2)+3,5),"")

Note that I have put a space either side of the state letters in the inner Find function - this will avoid picking up on any words which contain any of the two-letter state abbreviations (like the word aNY).

Copy this down column E as required.

Hope this helps.

Pete