You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"User" wrote in message
...
Thanks so much! Very impressive formula

"T. Valko" wrote:

If you don't mind, will you explain the formula?

Sure. This is a pretty slick formula once you "see" how it works.

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

A1 = Washington DC 20005

Let's break it down into steps:

The first thing that happens is the inner TRIM function gets executed.
TRIM
removes leading/trailing spaces and extra spaces between words in the
string. If A1 contained:

<spaceWashington<2 spacesDC<2 spaces20005<space

TRIM(A1) removes all those extra spaces so you end up with:

Washington DC 20005

That inner TRIM function might not be needed but it doesn't hurt anything
having it in there just in case!

The result of the inner TRIM function is then passed to the SUBSTITUTE
function. The SUBSTITUTE function lets you substitute character(s) in a
string with other characters.

SUBSTITUTE("Washington DC 20005"," ",REPT(" ",255))

In the above, we're going to substitute each instance of the space
character
with REPT(" ",255). The REPT function let's you repeat (REPT) a
character(s)
n times. In this case REPT(" ",255) means repeat a space character 255
times. So, what the SUBSTITUTE function is going to do is substitute each
instance of the space character in the string with 255 space characters.
That might sound kind of strange but you'll see later on just how that
comes
into play. Since I can't show all those spaces in this example (for
practical reasons!), this is what the result of the SUBSTITUTE function
looks like:

Washington_____DC_____20005

Just imagine that those underscores are 255 space characters.

Ok, the result of the SUBSTITUTE function is then passed to the RIGHT
function. The RIGHT function lets you extract a portion of the string
starting from the rightmost character and going to the left.

RIGHT("Washington_____DC_____20005",255)

That means we want to extract 255 characters starting from the rightmost
character in the string.

Remember, we used SUBSTITUTE to "pad" the number of spaces between words
so
there's <255 spaces20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces20005 = 255 characters

And we finally get to the last step!

The result of the RIGHT function is then passed to the outer TRIM
function
which removes those 200 leading spaces and the final result of the
formula
is:

20005

Pretty slick, ain't it? I don't know who the originator of that formula
is
but my hats off to them!

Why do we use 255 as arguments to REPT and RIGHT?

255 is just an arbitrary number that's large enough to insure that we get
the result we're looking for. We're assuming that the last word in the
string won't be more than 255 characters which is a fairly safe bet.


exp101
--
Biff
Microsoft Excel MVP


"User" wrote in message
...
Thank you!! After months of working on this off and on, you've finally
found
the answer!

If you don't mind, will you explain the formula? I'd like to learn why
it
works instead of just using copy and paste.

Thanks again!

"T. Valko" wrote:

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~