Sorting street addresses

M

Marta

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
 
T

Toppers

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
 
M

Marta

It doesn't seem to work for me, I get #VALUE error.

Toppers said:
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 said:
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
Click to expand...
Click to expand...
 
M

Marta

Ooops, now it's working - funny how one little digit can screw you up :D

Toppers said:
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 said:
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
Click to expand...
Click to expand...
 
V

Vince

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 said:
It doesn't seem to work for me, I get #VALUE error.

Toppers said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
V

Vince

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 said:
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 said:
It doesn't seem to work for me, I get #VALUE error.

Toppers said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Top