The database program I use exports an address into one field. I know about
text to columns and can seperate each line into a different text. My problem
now is that I can't seperate the zip code. Some address have a zip, some
have zip+4.

I've tried
=1*MID(H4,MATCH(TRUE,ISNUMBER(1*MID(H4,ROW(H:H),1) ),0),COUNT(1*MID(H4,ROW(H:H),1))) but that only seems to work on regular zip codes.

I've also tried =RIGHT(H7,FIND(" ",H7,1)+1) but it seems to give me
progressivly more characters (i.e. the zip code in cell I7, zip + 1 character
in I8, zip +2 characters in I9)

I adapted both of these from posts I found on the message board.

Thanks~

So you addresses have either a 5 digit Zip or a 9 digit Zip+4 at the end.
Try this in B1 with address in A1
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),"???"))

This will give you the Zip or Zip+4 (as a TEXT string) as the case may be or
??? if last 9 or 5 digits are not numbers.

Use
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

"User" wrote:

The database program I use exports an address into one field. I know about
text to columns and can seperate each line into a different text. My problem
now is that I can't seperate the zip code. Some address have a zip, some
have zip+4.

I've tried
=1*MID(H4,MATCH(TRUE,ISNUMBER(1*MID(H4,ROW(H:H),1) ),0),COUNT(1*MID(H4,ROW(H:H),1))) but that only seems to work on regular zip codes.

I've also tried =RIGHT(H7,FIND(" ",H7,1)+1) but it seems to give me
progressivly more characters (i.e. the zip code in cell I7, zip + 1 character
in I8, zip +2 characters in I9)

I adapted both of these from posts I found on the message board.

Thanks~

That worked for zip codes, but the zip+4 codes only displayed the dash and
last 4 digits.

"Sheeloo" wrote:

So you addresses have either a 5 digit Zip or a 9 digit Zip+4 at the end.
Try this in B1 with address in A1
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),"???"))

This will give you the Zip or Zip+4 (as a TEXT string) as the case may be or
??? if last 9 or 5 digits are not numbers.

Use
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

"User" wrote:

The database program I use exports an address into one field. I know about
text to columns and can seperate each line into a different text. My problem
now is that I can't seperate the zip code. Some address have a zip, some
have zip+4.

I've tried
=1*MID(H4,MATCH(TRUE,ISNUMBER(1*MID(H4,ROW(H:H),1) ),0),COUNT(1*MID(H4,ROW(H:H),1))) but that only seems to work on regular zip codes.

I've also tried =RIGHT(H7,FIND(" ",H7,1)+1) but it seems to give me
progressivly more characters (i.e. the zip code in cell I7, zip + 1 character
in I8, zip +2 characters in I9)

I adapted both of these from posts I found on the message board.

Thanks~

Show us some examples of the data.

--
Biff
Microsoft Excel MVP


"User" wrote in message
...
That worked for zip codes, but the zip+4 codes only displayed the dash and
last 4 digits.

"Sheeloo" wrote:

So you addresses have either a 5 digit Zip or a 9 digit Zip+4 at the end.
Try this in B1 with address in A1
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),"???"))

This will give you the Zip or Zip+4 (as a TEXT string) as the case may be
or
??? if last 9 or 5 digits are not numbers.

Use
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

"User" wrote:

The database program I use exports an address into one field. I know
about
text to columns and can seperate each line into a different text. My
problem
now is that I can't seperate the zip code. Some address have a zip,
some
have zip+4.

I've tried
=1*MID(H4,MATCH(TRUE,ISNUMBER(1*MID(H4,ROW(H:H),1) ),0),COUNT(1*MID(H4,ROW(H:H),1)))
but that only seems to work on regular zip codes.

I've also tried =RIGHT(H7,FIND(" ",H7,1)+1) but it seems to give me
progressivly more characters (i.e. the zip code in cell I7, zip + 1
character
in I8, zip +2 characters in I9)

I adapted both of these from posts I found on the message board.

Thanks~

A1: Washington DC 20005
A2: New York NY 10000-1234
A3: Portland ME 04923


When I use the above-mentioned formula, I get 20005 in B1 and -1234 in B2.


"T. Valko" wrote:

Show us some examples of the data.

--
Biff
Microsoft Excel MVP


"User" wrote in message
...
That worked for zip codes, but the zip+4 codes only displayed the dash and
last 4 digits.

"Sheeloo" wrote:

So you addresses have either a 5 digit Zip or a 9 digit Zip+4 at the end.
Try this in B1 with address in A1
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),"???"))

This will give you the Zip or Zip+4 (as a TEXT string) as the case may be
or
??? if last 9 or 5 digits are not numbers.

Use
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

"User" wrote:

The database program I use exports an address into one field. I know
about
text to columns and can seperate each line into a different text. My
problem
now is that I can't seperate the zip code. Some address have a zip,
some
have zip+4.

I've tried
=1*MID(H4,MATCH(TRUE,ISNUMBER(1*MID(H4,ROW(H:H),1) ),0),COUNT(1*MID(H4,ROW(H:H),1)))
but that only seems to work on regular zip codes.

I've also tried =RIGHT(H7,FIND(" ",H7,1)+1) but it seems to give me
progressivly more characters (i.e. the zip code in cell I7, zip + 1
character
in I8, zip +2 characters in I9)

I adapted both of these from posts I found on the message board.

Thanks~

Try this:

All on one line.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)
," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"User" wrote in message
...
A1: Washington DC 20005
A2: New York NY 10000-1234
A3: Portland ME 04923


When I use the above-mentioned formula, I get 20005 in B1 and -1234 in B2.


"T. Valko" wrote:

Show us some examples of the data.

--
Biff
Microsoft Excel MVP


"User" wrote in message
...
That worked for zip codes, but the zip+4 codes only displayed the dash
and
last 4 digits.

"Sheeloo" wrote:

So you addresses have either a 5 digit Zip or a 9 digit Zip+4 at the
end.
Try this in B1 with address in A1
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),"???"))

This will give you the Zip or Zip+4 (as a TEXT string) as the case may
be
or
??? if last 9 or 5 digits are not numbers.

Use
=IF(ISNUMBER(RIGHT(A1,9)*1),RIGHT(A1,9),IF(ISNUMBE R(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

"User" wrote:

The database program I use exports an address into one field. I
know
about
text to columns and can seperate each line into a different text.
My
problem
now is that I can't seperate the zip code. Some address have a zip,
some
have zip+4.

I've tried
=1*MID(H4,MATCH(TRUE,ISNUMBER(1*MID(H4,ROW(H:H),1) ),0),COUNT(1*MID(H4,ROW(H:H),1)))
but that only seems to work on regular zip codes.

I've also tried =RIGHT(H7,FIND(" ",H7,1)+1) but it seems to give me
progressivly more characters (i.e. the zip code in cell I7, zip + 1
character
in I8, zip +2 characters in I9)

I adapted both of these from posts I found on the message board.

Thanks~