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

**cmanlongat** · 05-10-2016, 02:56 PM

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.

**Mumps1** · 05-10-2016, 03:19 PM

Assuming that your data is in column A and that the word "ZIP" is somewhere in that column, try the following macro:

Sub FindZip()
    Application.ScreenUpdating = False
    Dim foundZip As Range
    Dim SplitZip As Variant
    Set foundZip = Range("A:A").Find("ZIP", LookIn:=xlValues, lookat:=xlPart)
    If Not foundZip Is Nothing Then
        SplitZip = Split(foundZip, ":")
        MsgBox SplitZip(1)
    End If
    Application.ScreenUpdating = True
End Sub

**cmanlongat** · 05-10-2016, 03:39 PM

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.

**FDibbins** · 05-10-2016, 03:41 PM

Hi, welcome to the forum

It would probably be a lot easier to help if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**cmanlongat** · 05-10-2016, 03:49 PM

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.

**Mumps1** · 05-10-2016, 03:55 PM

I had a look at your file but I didn't see the word "ZIP" or any zip codes in column D. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. Use this revised macro:

Sub FindZip()
    Application.ScreenUpdating = False
    Dim foundZip As Range
    Dim SplitZip As Variant
    Set foundZip = Range("D:D").Find("ZIP", LookIn:=xlValues, lookat:=xlPart)
    If Not foundZip Is Nothing Then
        SplitZip = Split(foundZip, ":")
        foundZip.Offset(0, 1) = SplitZip(1)
    End If
    Application.ScreenUpdating = True
End Sub

If it doesn't work the way you expect it, then please have another look at your file to make sure that the zip codes are included and re-post it.

**cmanlongat** · 05-10-2016, 04:12 PM

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.

**Pete_UK** · 05-10-2016, 03:58 PM

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

**Mumps1** · 05-10-2016, 04:11 PM

My apologies. Given the way that the data is organized, the macro I suggested won't work the way you want.

**cmanlongat** · 05-10-2016, 04:19 PM

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.

**Pete_UK** · 05-10-2016, 04:33 PM

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

**jindon** · 05-10-2016, 05:18 PM

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

Function GetUSZipCode(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b(A[LKZR]|C[AOT]|DE|FL|GA|HI|I[ADLN]|K[SY]|LA|M[AEDINSOT]" & _
        "|N[EVHJMYCD]|O[HRK]|PA|RI|S[CD]|T[NX]|[UT]V|VA|W[AVIY])[- ,]*(\d{5})\b"
        If .test(txt) Then GetUSZipCode = .Execute(txt)(0).submatches(1)
    End With
End Function

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Re: How do I extract a 5 digit zip code from a column that may have a random number of tex

Thread Information

Users Browsing this Thread

Similar Threads

Formula to extract 10 or 11 digit phone numbers from random complex test string

How to generate a random 5 digit number that does not already exist anywhere in a column

[SOLVED] 5-digit Random Unique Alphanumeric Code

[SOLVED] 5-digit Random Unique Alphanumeric Code

[SOLVED] extract 10 digit number only from text string

[SOLVED] VBA to convert cell to text and ensuring 7 digit number all along in entire column.

[SOLVED] Random 8 digit number?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions