Extracting House Numbers from Street Addresses

[George M. Fodor]

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.

 

[Gary''s Student]

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

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

 

[George M. Fodor]

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]

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

 

[George M. Fodor]

Excellent!

Thank you so much for your help.

George M.

--