Need Macro to pull out Zip Code

[ADeYoung]

Need a Macro that will pull out a zip code from a cell here is the example:

Address: 400 NE 3rd St City: Prineville County: Crook County State: OR Zip code: 97754-1921

This is imported into excel as one cell...I want to pull out and sort by zip code, but because some only have 5 didgets and others 10, I have failed at trying to pull it out.

 

[Cbrine]

Here's quick thing that might help you.

Try using text to columns, from the tool menu.

Set you delimiter as ":" and run it on the column. If the zip code: is always your last value, it should dump it into it's own cell. You might have some alignment issues, if the number of : fluctuates.

HTH.

 

[Norie]

If this is the exact format you could try doing Data>Text to Columns... twice

First seperate address parts out with ':' as a delimiter.

Then seperate the zip codes with'-' as a delimeter.

 

[Seti]

This macro will run on one cell and replace the contents with everything after the last : and assumes a space after the colon that is ignored as well.

Sub FindZip()
Dim length As Integer, i As Integer
data = ActiveCell.Value
For i = Len(data) To 1 Step -1
    If Mid(data, i, 1) = ":" Then
        length = i
        Exit For
    End If
Next i
data = Mid(data, length + 2, Len(data) - length)
ActiveCell.Value = data
End Sub

 

[ADeYoung]

Thanks...that worked!

 

[ddubnansky]

I was trying to figure out this dilemma by using the RIGHT and LEN functions combined. I gave up and had a stroke of dumb luck befall me when I tried something else. I know this probably isn't what you want to do. But if you just want the Zip Codes to be shown, you could you the FIND/REPLACE option. Press CTRL+H and in the FIND field type "*: " (do not include the "s but make sure to include the space after the colon). This will remove everything except the ZipCode.