Extracting House Numbers from Street Addresses
 

How do I extract house numbers from street addresses when the number of
characters varies.

For example, one address might be "4 Olde Coach Road," another might be "22
Olde Coach Road" or even "138 Cyber Lane" or even "1452 Digital Drive." At
times, there is even the odd "15 L Byte Boulevard."

Ultimately, I need to sort my list by street name, and to do so, I need to
extract the various leading numbers.

Thanks for your help.

--
George M.
gmfbard AT verizon DOT net

Extracting House Numbers from Street Addresses
 

All your examples have a number followed by a blank followed by the rest of
the address:

=LEFT(A1,FIND(" ",A1)-1)

--
Gary''s Student - gsnu200812

Extracting House Numbers from Street Addresses
 

Thank you for your suggestion. This does and excellent job of displaying the
house number.

What I'd really like to do, though, is wind up with the street name, so that
I can sort on that value.

George M.

"Gary''s Student" wrote in message
...
All your examples have a number followed by a blank followed by the rest
of
the address:

=LEFT(A1,FIND(" ",A1)-1)

--
Gary''s Student - gsnu200812

Extracting House Numbers from Street Addresses
 

Hi George:

Quite easy. Our first formula looked for the first blank and display the
housenumber to the LEFT of the blank. This formula displays "the rest of the
story":

=MID(A1,FIND(" ",A1)+1,9999)


Thus if A1 contains 123 North Maple Avenue
then
=LEFT(A1,FIND(" ",A1)-1) will display 123
=MID(A1,FIND(" ",A1)+1,9999) will display North Maple Avenue


then
--
Gary''s Student - gsnu200812


"George M. Fodor" wrote:

Thank you for your suggestion. This does and excellent job of displaying the
house number.

What I'd really like to do, though, is wind up with the street name, so that
I can sort on that value.

George M.

"Gary''s Student" wrote in message
...
All your examples have a number followed by a blank followed by the rest
of
the address:

=LEFT(A1,FIND(" ",A1)-1)

--
Gary''s Student - gsnu200812

Extracting House Numbers from Street Addresses
 

Excellent!

Thank you so much for your help.

George M.

--
"Gary''s Student" wrote in message
...
Hi George:

Quite easy. Our first formula looked for the first blank and display the
housenumber to the LEFT of the blank. This formula displays "the rest of
the
story":

=MID(A1,FIND(" ",A1)+1,9999)


Thus if A1 contains 123 North Maple Avenue
then
=LEFT(A1,FIND(" ",A1)-1) will display 123
=MID(A1,FIND(" ",A1)+1,9999) will display North Maple Avenue


then
--
Gary''s Student - gsnu200812


"George M. Fodor" wrote:

Thank you for your suggestion. This does and excellent job of displaying
the
house number.

What I'd really like to do, though, is wind up with the street name, so
that
I can sort on that value.

George M.

"Gary''s Student" wrote in
message
...
All your examples have a number followed by a blank followed by the
rest
of
the address:

=LEFT(A1,FIND(" ",A1)-1)

--
Gary''s Student - gsnu200812