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~