sorting an excel address list with firstname lastname issues

[psg2103]

hi i use osx10.3.3, a G4 with 500-odd MB of memory and excel 2004 for
mac version 11.1.1
i have a problem trying to sort an address list database. i am an excel
neophyte and need your help!

the problem: i have a list of some 800 addresses for my high school
reunion mailing. the fellow who made up the list combined first names
and last names into one data column. that means the list sorts by first
name. this has some advantages - women who married (and bizarrely the
significant number of guys who changed their family names!) can be more
easily found. however, for various other reasons, i need to do two
things with this list:

1 - move it into an address book format: entourage, apple address book
or palm desktop address book;
2 - to move it into an address book, i need to separate the first and
last names into distinct fields.

is there any formula or macro that i can run (or download and run)
thatw oudl allow me to separate the last word (i.e. the last name) in
each entry (row) and create a separate column with last names?

finally can someone help me figure out how to "lock" the relationship
of the database, so that if i sort any one column, or change or delete
entries, the data from each row remains locked together (does that make
sense? i mean that if i remove one person's address all the other
addresses bump up, switching the names tow hich they are attached. if
i sort by zip code, the rest of the data stays where it is and everyone
gets a new zip code. thanks - peter

 

[CyberTaz]

Hello-

Not as much a problem as it seems. No formulas or macros are necessary.

First, select a cell to the right of the names, then Insert>Column. That
will give you an empty column between the current name column & whatever is
next.

Next, select the column of names not including the caption at the top of
that column (click the top name, then Shift+Cmd+DownArrow is about the
quickest I know of) and use Data>Text to Columns, specifying Fixed Width
since the first & last names are separated by a space. If there are records
with middle names or initials, adjust as necessary. That should take care of
the list.

Make sure you have the data in consecutive columns & consecutive rows with
captions at the top of each column (No empty rows or columns in between).

To sort the list on any 'field', click in that column and use the A-Z or Z-A
Quick Sort buttons on the toolbar or go to Data>Sort to sort on up to 3
fields. *Do Not* select an entire column because that will sort only the
data in that column. There is no need to 'lock' anything for sorting
purposes.

If you need to delete a complete record, click the Row Number Heading of the
row it is on or select the cells occupied by the record and use Edit>Delete.
All rows below it will shift up. BTW- There is also a feature called the
Data Form (Data>Form) that you can use as long as the cell selector is
somewhere in the range of records... You might want to take a look at it. If
you just want to delete the content of a cell but retain the rest of the
record, select the cell(s) and press the del (not delete) key.

Save your work as you go, but examine the features of the program you intend
to use for address purposes. You may be able to access the Excel data
directly without doing anything more. MS Word, for example, can read from an
Excel file for mail merge, including form letters, labels & envelopes.

If you want the data in Entourage, however, you will have to use Excel's
File>Save As dialog box to save a copy of the data in a CSV(Comma Delimited)
format. Then use Entourage's File>Import command to read the records in.

HTH |:>)