HELP a computer illiterate person with the following:
I have some mailing lists in an excel file, Cell B is the address. Here is an example of what I want to do:
This is how it currently is:
567 main street
234 main street
456 elm street
123 oak street
789 main street
345 elm street
234 elm street
I want to alphabetize this by street name first…THEN have the house number in numeric order withing each street name. So that I will have elm street in numeric order, then main street in numeric order, then oak street in numeric order.
This is how I want it to be:
234 elm street
345 elm street
456 elm street
234 main street
567 main street
789 main street
123 oak street
Certainly this can’t be hard to do. But for me, it’s like rocket science. I have to be able to locate addresses a lot easier, and manage these files better.
I do not know if this is a option for you, but it sounds like it might be easier for you to use Microsoft Access to do this mailing list. I am not sure how to alphabetize in excel, but it is quite easy in Access. Just my two cents.
highlight all the address rows (put your cursor on the first address press your left mouse key and scroll to the last address)
click on the Data option in the menu at the top of the page and choose Text to Columns
Select the Delimited option in the pop-up box and click next
Select the Space delimiter option and click next then just finish
this will seperate the street number from the street name etc…
to sort the result by multiple columns do this:
highlight all the rows and columns containing data to be sorted
click on the Data option in the menu at the top of the page and choose sort
select up to three columns to be sorted in order
Microsoft Access is a powerful database tool but it creates a whole new learning curve. So long as you have less than 65,000 rows of data excel can handle pretty much anything you throw at it. You can even pump the addresses from excel into Microsoft Word and create mailing labels.
71tr, I did what you said, and the columns were separated, but when they were, the columns to the right disappeared. Do I need to have the next cell be empty before I separate? In other words, do I have to move them over a spot (the ones next to them)? Thanks.
If I have to, any help in how would be appreciated.
Yes, it sounds like the columns were overwritten when excel seperated the addresses into seperate new columns. If you haven’t saved the new work you can simply reopen the spreadsheet and start over. This time insert a few columns between the address and any data to the right. This will create a blank slate for excel to fill the seperated address values.
If you right click on the column header (A, B, C) and select insert this will squeeze a blank column into the spot.
i would recommend moving column a to the end. if you are using columbs a,b,c,d,e,f, then moved it to column g by selecting column a. right click. choose cut, then select column e, right click, select insert cut cells. then do the text to columns. once that is done, sort it how you want. then you can re-combine the cells by entering a formula such as =g1& " “& h1&” " i1 into column j. then select column j, right click, select copy, then right click again and select paste special. choose paste values.