| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting street addresses
Here's my dilema: I have spreasheet full of addresses. I'd like to sort the
street address column by street name, however the column includes both the street number and street address. Any idea how to do that? Here's a sample of my spreadsheet: 1975 Glenada Cres 351 Glenashton Dr 2012 Grand Blvd 2135 Pineview Dr 2154 Pinevalley Cres 1017 Glenbrook Ave 1057 Glenbrook Ave 134 Glenashton Dr 1442 Creekwood Trail 1558 Lancaster Dr 2015 Grand Blvd 2027 Highridge Crt 1356 Summerhill Cres 2204 Golden Briar Trail 1542 Lancaster Dr 2012 Grand Blvd 1414 Bayshire Dr 1550 Lancaster Dr 1486 Grand Blvd 1480 Grand Blvd 2124 Pineview Dr 2121 Pinevalley Cres 1580 Lancaster Dr 1102 Grandeur Cres Thanks, Marta |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting street addresses
You will need to separate the street number and street address into two
columns. For street number: (say column B) =left(A1,find( " ",A1)-1) for street name: (say Column C) =Mid(A1,find(" ",A1)+1,255) Sort on column C and [then] B to get Street Name/Street Number sequence HTH "Marta" wrote: Here's my dilema: I have spreasheet full of addresses. I'd like to sort the street address column by street name, however the column includes both the street number and street address. Any idea how to do that? Here's a sample of my spreadsheet: 1975 Glenada Cres 351 Glenashton Dr 2012 Grand Blvd 2135 Pineview Dr 2154 Pinevalley Cres 1017 Glenbrook Ave 1057 Glenbrook Ave 134 Glenashton Dr 1442 Creekwood Trail 1558 Lancaster Dr 2015 Grand Blvd 2027 Highridge Crt 1356 Summerhill Cres 2204 Golden Briar Trail 1542 Lancaster Dr 2012 Grand Blvd 1414 Bayshire Dr 1550 Lancaster Dr 1486 Grand Blvd 1480 Grand Blvd 2124 Pineview Dr 2121 Pinevalley Cres 1580 Lancaster Dr 1102 Grandeur Cres Thanks, Marta |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting street addresses
It doesn't seem to work for me, I get #VALUE error.
"Toppers" wrote: You will need to separate the street number and street address into two columns. For street number: (say column B) =left(A1,find( " ",A1)-1) for street name: (say Column C) =Mid(A1,find(" ",A1)+1,255) Sort on column C and [then] B to get Street Name/Street Number sequence HTH "Marta" wrote: Here's my dilema: I have spreasheet full of addresses. I'd like to sort the street address column by street name, however the column includes both the street number and street address. Any idea how to do that? Here's a sample of my spreadsheet: 1975 Glenada Cres 351 Glenashton Dr 2012 Grand Blvd 2135 Pineview Dr 2154 Pinevalley Cres 1017 Glenbrook Ave 1057 Glenbrook Ave 134 Glenashton Dr 1442 Creekwood Trail 1558 Lancaster Dr 2015 Grand Blvd 2027 Highridge Crt 1356 Summerhill Cres 2204 Golden Briar Trail 1542 Lancaster Dr 2012 Grand Blvd 1414 Bayshire Dr 1550 Lancaster Dr 1486 Grand Blvd 1480 Grand Blvd 2124 Pineview Dr 2121 Pinevalley Cres 1580 Lancaster Dr 1102 Grandeur Cres Thanks, Marta |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting street addresses
Ooops, now it's working - funny how one little digit can screw you up :D
"Toppers" wrote: You will need to separate the street number and street address into two columns. For street number: (say column B) =left(A1,find( " ",A1)-1) for street name: (say Column C) =Mid(A1,find(" ",A1)+1,255) Sort on column C and [then] B to get Street Name/Street Number sequence HTH "Marta" wrote: Here's my dilema: I have spreasheet full of addresses. I'd like to sort the street address column by street name, however the column includes both the street number and street address. Any idea how to do that? Here's a sample of my spreadsheet: 1975 Glenada Cres 351 Glenashton Dr 2012 Grand Blvd 2135 Pineview Dr 2154 Pinevalley Cres 1017 Glenbrook Ave 1057 Glenbrook Ave 134 Glenashton Dr 1442 Creekwood Trail 1558 Lancaster Dr 2015 Grand Blvd 2027 Highridge Crt 1356 Summerhill Cres 2204 Golden Briar Trail 1542 Lancaster Dr 2012 Grand Blvd 1414 Bayshire Dr 1550 Lancaster Dr 1486 Grand Blvd 1480 Grand Blvd 2124 Pineview Dr 2121 Pinevalley Cres 1580 Lancaster Dr 1102 Grandeur Cres Thanks, Marta |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting street addresses
Marta, it worked for me and may solve a problem that I have.
Check your typing or use copy and paste for each function (remove any leading space before the = sign). Copy and paste the first function in B1 enter and autofill. Copy and paste the second function in C1 enter and autofill. Then sort. Thanks Toppers for this useful method of creating two columns from one column with a space delimiter (or any other delimiter). -- Vince "Marta" wrote: It doesn't seem to work for me, I get #VALUE error. "Toppers" wrote: You will need to separate the street number and street address into two columns. For street number: (say column B) =left(A1,find( " ",A1)-1) for street name: (say Column C) =Mid(A1,find(" ",A1)+1,255) Sort on column C and [then] B to get Street Name/Street Number sequence HTH "Marta" wrote: Here's my dilema: I have spreasheet full of addresses. I'd like to sort the street address column by street name, however the column includes both the street number and street address. Any idea how to do that? Here's a sample of my spreadsheet: 1975 Glenada Cres 351 Glenashton Dr 2012 Grand Blvd 2135 Pineview Dr 2154 Pinevalley Cres 1017 Glenbrook Ave 1057 Glenbrook Ave 134 Glenashton Dr 1442 Creekwood Trail 1558 Lancaster Dr 2015 Grand Blvd 2027 Highridge Crt 1356 Summerhill Cres 2204 Golden Briar Trail 1542 Lancaster Dr 2012 Grand Blvd 1414 Bayshire Dr 1550 Lancaster Dr 1486 Grand Blvd 1480 Grand Blvd 2124 Pineview Dr 2121 Pinevalley Cres 1580 Lancaster Dr 1102 Grandeur Cres Thanks, Marta |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting street addresses
Marta, column B will be in text format and it may be better to use number
format by copying and pasting a value to B1 =VALUE(LEFT(A1,FIND(" ",A1)-1)) Column B cells should be formatted for number with no decimal places. -- Vince "Vince" wrote: Marta, it worked for me and may solve a problem that I have. Check your typing or use copy and paste for each function (remove any leading space before the = sign). Copy and paste the first function in B1 enter and autofill. Copy and paste the second function in C1 enter and autofill. Then sort. Thanks Toppers for this useful method of creating two columns from one column with a space delimiter (or any other delimiter). -- Vince "Marta" wrote: It doesn't seem to work for me, I get #VALUE error. "Toppers" wrote: You will need to separate the street number and street address into two columns. For street number: (say column B) =left(A1,find( " ",A1)-1) for street name: (say Column C) =Mid(A1,find(" ",A1)+1,255) Sort on column C and [then] B to get Street Name/Street Number sequence HTH "Marta" wrote: Here's my dilema: I have spreasheet full of addresses. I'd like to sort the street address column by street name, however the column includes both the street number and street address. Any idea how to do that? Here's a sample of my spreadsheet: 1975 Glenada Cres 351 Glenashton Dr 2012 Grand Blvd 2135 Pineview Dr 2154 Pinevalley Cres 1017 Glenbrook Ave 1057 Glenbrook Ave 134 Glenashton Dr 1442 Creekwood Trail 1558 Lancaster Dr 2015 Grand Blvd 2027 Highridge Crt 1356 Summerhill Cres 2204 Golden Briar Trail 1542 Lancaster Dr 2012 Grand Blvd 1414 Bayshire Dr 1550 Lancaster Dr 1486 Grand Blvd 1480 Grand Blvd 2124 Pineview Dr 2121 Pinevalley Cres 1580 Lancaster Dr 1102 Grandeur Cres Thanks, Marta |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help! How to sort addresses by street then by #...... | New Users to Excel | |||
| Sorting IP Addresses in Excel? | Excel Discussion (Misc queries) | |||
| sorting IP addresses | Excel Discussion (Misc queries) | |||
| Working with street addresses? | Excel Discussion (Misc queries) | |||
| Problems copying street addresses and dates | Excel Discussion (Misc queries) | |||
Linear Mode
