new user needs help
 

it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the number
so I would have the sheet list them in order of street name and then order
of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the name
alphabetically and then the number numerically

please and thank you...

--


-----------------------------------------------------
Lawyer to witness: Now doctor, isn't it true that when a person dies in his
sleep, he doesn't know about it until the next morning?
-----------------------------------------------------

Bill H. in Chicagoland
webcams at http://76.16.160.118:8080
weather at http://hhweather.webhop.org
web blog at http://vote4billheinzinger.blogsport.com

new user needs help
 

Have you looked in the help index for SORT

--
Don Guillett
SalesAid Software

"FireBrick" wrote in message
...
it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the
number so I would have the sheet list them in order of street name and
then order of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the
name alphabetically and then the number numerically

please and thank you...

--


-----------------------------------------------------
Lawyer to witness: Now doctor, isn't it true that when a person dies in
his sleep, he doesn't know about it until the next morning?
-----------------------------------------------------

Bill H. in Chicagoland
webcams at http://76.16.160.118:8080
weather at http://hhweather.webhop.org
web blog at http://vote4billheinzinger.blogsport.com

new user needs help
 

To split the columns go to DateText to Columns.
Next step depends on how the info was entered if you can use fixed with or
not.
If not select delimitedspacefinish.
Now you will have to concatenate the address back together with spaces.
Not fun, but the only way I know.

--
Mike Q.


"FireBrick" wrote:

it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the number
so I would have the sheet list them in order of street name and then order
of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the name
alphabetically and then the number numerically

please and thank you...

--


-----------------------------------------------------
Lawyer to witness: Now doctor, isn't it true that when a person dies in his
sleep, he doesn't know about it until the next morning?
-----------------------------------------------------

Bill H. in Chicagoland
webcams at http://76.16.160.118:8080
weather at http://hhweather.webhop.org
web blog at http://vote4billheinzinger.blogsport.com

new user needs help
 

If your data is as simple as the example, you could use DataText to Columns to
split the number away from the street name.

But I'll bet it is more complicated than you show.

Post a real example if of the more complicated type.


Gord Dibben MS Excel MVP

On Mon, 5 Mar 2007 11:25:05 -0600, "FireBrick" wrote:

it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the number
so I would have the sheet list them in order of street name and then order
of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the name
alphabetically and then the number numerically

please and thank you...

new user needs help
 

learning on the fly here
that worked, thank you guys.

sorry I didn't know how to post the question properly.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
If your data is as simple as the example, you could use DataText to
Columns to
split the number away from the street name.

But I'll bet it is more complicated than you show.

Post a real example if of the more complicated type.


Gord Dibben MS Excel MVP

On Mon, 5 Mar 2007 11:25:05 -0600, "FireBrick"
wrote:

it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the
number
so I would have the sheet list them in order of street name and then order
of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the
name
alphabetically and then the number numerically

please and thank you...

new user needs help
 

Thanks
Just one more question
How do I get it to do one side of the street and then the other
I mean, say take all even numbered addresses and then all odd numbered.

That's far past my ability right now...



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
If your data is as simple as the example, you could use DataText to
Columns to
split the number away from the street name.

But I'll bet it is more complicated than you show.

Post a real example if of the more complicated type.


Gord Dibben MS Excel MVP

On Mon, 5 Mar 2007 11:25:05 -0600, "FireBrick"
wrote:

it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the
number
so I would have the sheet list them in order of street name and then order
of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the
name
alphabetically and then the number numerically

please and thank you...

new user needs help
 

If the numeric address is in column A, starting in row 2.
Paste this formula in row 2 of an empty column.
=MOD(A2,2)
Now drag the formula down. Even is 0, Odd is 1.
Use DataFilter to show odds or evens.
--
Mike Q.


"FireBrick" wrote:

Thanks
Just one more question
How do I get it to do one side of the street and then the other
I mean, say take all even numbered addresses and then all odd numbered.

That's far past my ability right now...



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
If your data is as simple as the example, you could use DataText to
Columns to
split the number away from the street name.

But I'll bet it is more complicated than you show.

Post a real example if of the more complicated type.


Gord Dibben MS Excel MVP

On Mon, 5 Mar 2007 11:25:05 -0600, "FireBrick"
wrote:

it's a spreadsheet of names and addresses
the addresses column has both the street number and address.
like 1233 LAKE ST

What I would like to do, is sort them on the street name AND THEN the
number
so I would have the sheet list them in order of street name and then order
of number
such as
1233 Lake St
1234 Lake St
1235 Lake St
etc.

so how do I split the columns automatically so that it will sort by the
name
alphabetically and then the number numerically

please and thank you...