isolate a number in cell with text and numbers

[User]

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~

 

[Sheeloo]

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(ISNUMBER(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(ISNUMBER(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

 

[Sheeloo]

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(ISNUMBER(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(ISNUMBER(RIGHT(A1,5)*1),RIGHT(A1,5),""))
if you want a blank in place of ???

You can copy the formula down.

 

[User]

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

 

[User]

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

 

[T. Valko]

Show us some examples of the data.

 

[T. Valko]

Show us some examples of the data.

 

[User]

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.

 

[User]

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]

Try this:

All on one line.

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

 

[T. Valko]

Try this:

All on one line.

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

 

[User]

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!

 

[User]

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]

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:

<space>Washington<2 spaces>DC<2 spaces>20005<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 spaces>20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces>20005 = 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

 

[T. Valko]

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:

<space>Washington<2 spaces>DC<2 spaces>20005<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 spaces>20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces>20005 = 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

 

[User]

Thanks! Very impressive 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:

<space>Washington<2 spaces>DC<2 spaces>20005<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 spaces>20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces>20005 = 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

 

[User]

Thanks so much! Very impressive 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:

<space>Washington<2 spaces>DC<2 spaces>20005<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 spaces>20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces>20005 = 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

 

[User]

Thanks! Very impressive 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:

<space>Washington<2 spaces>DC<2 spaces>20005<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 spaces>20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces>20005 = 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

 

[User]

Thanks so much! Very impressive 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:

<space>Washington<2 spaces>DC<2 spaces>20005<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 spaces>20005 or a total of 260 characters. We told RIGHT we
wanted the last 255 characters so the result of the RIGHT function is:

<200 spaces>20005 = 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

 

[T. Valko]

You're welcome. Thanks for the feedback!