Extract ZIP code after State abbreviation

Question

I have an Excel file with the full mailing address of several customers, and I need to fill a column with only the 5-digit zip codes, which always follow the same state abbreviation, "TX." Sometimes a 'phone number is added behind the zip code, and sometimes it isn't.

If there is a way to select 5 digits after the TX and move that information to a new cell, down to the last row, I haven't found it yet. I'm completely new to VBA, just need it for a one time job. Example:

D3 has Namey Name Streety Street TX 75029 432-239-2490
D4 has Namity Names Streetick Street TX 73902

I want to move 75029 to F3 and 73902 to F4 respectively.

http://stackoverflow.com/q/7293461/62576 shows you how to extract part of a string. You should be able to easily modify it to work with finding the digits after `Tx `. — Ken White, Mar 06 '15 at 00:05

score 3 · Answer 1 · answered Mar 06 '15 at 00:30

3

"for a one time job" VBA seems overkill and even a formula not necessary. Just copy ColumnD into ColumnF, select ColumnF and with Replace... (HOME > Editing, Find & Replace), Find what:

*TX

(there is a space after the X),

Replace All, followed by Find what:

*

(there is a space before the *)

Replace All.

answered Mar 06 '15 at 00:30

pnuts

58,317
11
87
139

1

Very nice answer pnuts. I will file this one away for my own use :). – Dan Donoghue Mar 06 '15 at 03:07

score 1 · Answer 2 · answered Mar 06 '15 at 00:06

1

Something like this excel formula?

=MID(D3,FIND(" TX ",D3)+4,5)

in F3 Then drag down

answered Mar 06 '15 at 00:06

Dan Donoghue

6,056
2
18
36

score 0 · Answer 3 · answered Mar 06 '15 at 00:06

Replace s with your input and put zip where you want it to go.

    Dim s As String
    Dim idx As Long
    Dim zip As String

    s = "Namey Name Streety Street TX 75029 432-239-2490"

    idx = InStr(1, UCase(s), " TX ")

    If idx > 0 Then
        zip = Mid(s, idx + 4, 5)
    Else
        zip = "Not found"
    End If

Extract ZIP code after State abbreviation

3 Answers3