I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?

You need to parse the street number from the street name and then sort them. The quickest way to do this to use DATATEXT TO COLUMNS
from the Excel menu. This will give you the choice of either parsing by fixed number of characters or by some delimiter, like a
space. When I have done this, I have always encountered people in the list who have an address which begins with a company name or
"c/o", or something like that.

Another option for you is to write a formula that will search for the first space and return everything after that. You would be in
a better position to know which would work best for you. If you feel the formula route would help the most but need help writing it,
let me know and I'll give you an example.

Regardless of which route you go, you can send the results to Access after that if you want. However, Excel can handle this job
easily and is probably just as easy to deal with capitalization issues and oddball exceptions from the normal record.
--
RMC,CPA


"justaMom" wrote in message ...
I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?

I am sorry to be so dumb, but what is parsing? and if I do that will I lose
the house number, or will it be in a different column. I don't want to lose
the house number.

"R. Choate" wrote:

You need to parse the street number from the street name and then sort them. The quickest way to do this to use DATATEXT TO COLUMNS
from the Excel menu. This will give you the choice of either parsing by fixed number of characters or by some delimiter, like a
space. When I have done this, I have always encountered people in the list who have an address which begins with a company name or
"c/o", or something like that.

Another option for you is to write a formula that will search for the first space and return everything after that. You would be in
a better position to know which would work best for you. If you feel the formula route would help the most but need help writing it,
let me know and I'll give you an example.

Regardless of which route you go, you can send the results to Access after that if you want. However, Excel can handle this job
easily and is probably just as easy to deal with capitalization issues and oddball exceptions from the normal record.
--
RMC,CPA


"justaMom" wrote in message ...
I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?

Parsing means seperating. You won't lose anything. Besides, if you don't like the results, don't save. Also, I would save a seperate
working copy anyway so I can go back to the original if anything bad happens along the way. Using the text to columns is probably
the best way for you to start parsing your data once you have made your "safe copy". It will make several columns from the single
column of data you started with. There is a wizard to guide you through the process.

HTH
--
RMC,CPA


"justaMom" wrote in message ...
I am sorry to be so dumb, but what is parsing? and if I do that will I lose
the house number, or will it be in a different column. I don't want to lose
the house number.

"R. Choate" wrote:

You need to parse the street number from the street name and then sort them. The quickest way to do this to use DATATEXT TO
COLUMNS
from the Excel menu. This will give you the choice of either parsing by fixed number of characters or by some delimiter, like a
space. When I have done this, I have always encountered people in the list who have an address which begins with a company name or
"c/o", or something like that.

Another option for you is to write a formula that will search for the first space and return everything after that. You would be
in
a better position to know which would work best for you. If you feel the formula route would help the most but need help writing
it,
let me know and I'll give you an example.

Regardless of which route you go, you can send the results to Access after that if you want. However, Excel can handle this job
easily and is probably just as easy to deal with capitalization issues and oddball exceptions from the normal record.
--
RMC,CPA


"justaMom" wrote in message ...
I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?

By jove I did it!!! Thank you so much!

"R. Choate" wrote:

Parsing means seperating. You won't lose anything. Besides, if you don't like the results, don't save. Also, I would save a seperate
working copy anyway so I can go back to the original if anything bad happens along the way. Using the text to columns is probably
the best way for you to start parsing your data once you have made your "safe copy". It will make several columns from the single
column of data you started with. There is a wizard to guide you through the process.

HTH
--
RMC,CPA


"justaMom" wrote in message ...
I am sorry to be so dumb, but what is parsing? and if I do that will I lose
the house number, or will it be in a different column. I don't want to lose
the house number.

"R. Choate" wrote:

You need to parse the street number from the street name and then sort them. The quickest way to do this to use DATATEXT TO
COLUMNS
from the Excel menu. This will give you the choice of either parsing by fixed number of characters or by some delimiter, like a
space. When I have done this, I have always encountered people in the list who have an address which begins with a company name or
"c/o", or something like that.

Another option for you is to write a formula that will search for the first space and return everything after that. You would be
in
a better position to know which would work best for you. If you feel the formula route would help the most but need help writing
it,
let me know and I'll give you an example.

Regardless of which route you go, you can send the results to Access after that if you want. However, Excel can handle this job
easily and is probably just as easy to deal with capitalization issues and oddball exceptions from the normal record.
--
RMC,CPA


"justaMom" wrote in message ...
I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?

If you like, send the workbook to me and I will sort it
and return it to you. Remove XXX from my email address.

http://www.officeletter.com/blink/specialsort.html
--
Jim Cone
San Francisco, USA
XX



"justaMom"
wrote in message
...
I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?