Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to House, 123 Street, Somewhere, M1 23X | Excel Discussion (Misc queries) | |||
how do I look for duplicate house addresses in a column in Excel? | Excel Worksheet Functions | |||
TRYING TO ALPHBETIZE STREET ADDRESSES | Excel Discussion (Misc queries) | |||
Help! How to sort addresses by street then by #...... | New Users to Excel | |||
Removing house numbers from addresses | Excel Worksheet Functions |