isolate a number in cell with text and numbers

U

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~
 
S

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.
 
U

User

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

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

T. Valko

Try this:

All on one line.

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

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

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
 
U

User

Thanks! Very impressive formula :)

T. Valko said:
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
Click to expand...
 
U

User

Thanks so much! Very impressive formula

T. Valko said:
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
Click to expand...
 
A

AJ

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

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replacing Text in a cell with blanks 2
Splitting text and numbers from a cell 6
How can I isolate the last two words in a text string? 7
Function to put worksheet name in cell returns text, need number 5
text not always in same cell 2
Isolate text immediately preceding "(" 7
How do I find a dash in a cell with numbers in it in excel. 9
Tag Numbers 7

Top