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~

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~

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~

Thanks! 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~

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~

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~

Hi

Sorry to butt in on this one - I have used your suggestion to extract
numbers - very good method!

However, with the result (which is two cells added together) I would now
like to put another text sting in front of the result, i.e. ="Total =
"&...your extraction forumula here...

However, this only gives me the text, but not the total number which I get
without trying to add the text string at the front.

I have looked at many of the other mind boggling ways of extracting numbers,
but like this one as it's fairly straight forwards - especially as you
explained it!!

Many thanks

Andrew

"T. Valko" wrote:

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~

That formula doesn't specifically extract numbers per se, it extracts the
last "word" from a string. In this thread the OP's data just happened to
have a string of numbers as *the last word*.

So, if:

A1 = The total for January is 100

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

Returns:

Total = 100

--
Biff
Microsoft Excel MVP


"AJ" wrote in message
...
Hi

Sorry to butt in on this one - I have used your suggestion to extract
numbers - very good method!

However, with the result (which is two cells added together) I would now
like to put another text sting in front of the result, i.e. ="Total =
"&...your extraction forumula here...

However, this only gives me the text, but not the total number which I get
without trying to add the text string at the front.

I have looked at many of the other mind boggling ways of extracting
numbers,
but like this one as it's fairly straight forwards - especially as you
explained it!!

Many thanks

Andrew

"T. Valko" wrote:

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~