Database name field seperation into salutation, first, last.

[rglocke]

I have a database of name, address and city/state/zip. The name field is:
Mr. and Mrs. John Doe. I need separate fields with salutation, first and last name. I can not find the formulas to do these. Help!

[Barry]

I have a database of name, address and city/state/zip. The name field is:
Mr. and Mrs. John Doe. I need separate fields with salutation, first and last name. I can not find the formulas to do these. Help!

Your example is well chosen to illustrate the difficulty of this task.

There is no direct formula, as AppleWorks has no way of recognizing what portion of the text forms each part of the name.

If there's no variety in the name format throughout the DB, then a combination of the FIND(), LEN(), LEFT() and RIGHT() functions can be used to separate the words into separate fields. But this doesn't seem practical for your list, as you likely have a variety of formats similar to those listed below:

Mr. and Mrs. John Doe
Mr. Richard Roe
Mrs. Janet Wilson Smith
Miss J. Hunter-Dunn

The technique is to use FIND() to determine the position of the first space in the field's contents, then use the other functions to separate the contents into two fields—one containing everything before the first space, the other everything following the first space—then repeat as necessary.

For a DB with all names following the Richard Roe format above, the following set of fields and formulas would separate the elements into three separate fields.

'Hon' LEFT('Name',FIND(" ",'Name'))
     Extracts the Honorific—everything up to the first space

'Name2' RIGHT('Name',LEN('name')-FIND(" ",'Name'))
     Temporary holding field for the rest of the contents of 'Name'

'First' LEFT('Name2',FIND(" ",'Name2'))
Same formula as 'Hon', but using 'Name2' as source.

'Last' RIGHT('Name2',LEN('Name2')-FIND(" ",'Name2'))
    Same formula as 'Name2', but using 'Name2' as source.

For the list above, this would also give correct results for Miss Hunter-Dunn (a fictional tennis player in a spoken song by Michael Flanders and Donald Swann).

But Janet's last name would become "Wilson Smith", and the unfortunate Does would be listed as

Hon:    Mr.
First:    and
Last:    Mrs. John Doe


I don't see an easy solution to Janet, but the Doe problem is fairly easy to side step. As the Find() function searches for the first space in each 'Name' entry, the solution is to ensure there are no space characters for it to find before the one preceding the first name.

With nothing selected in the database, press command-F to get the Find/Change dialogue.

Enter " and " (omit the quotation marks, but include the single space before and after the word and) into the Find box.

Enter " and " into the Change box. This LOOKS very much the same, but the 'spaces' before and after 'and' are 'sticky spaces' typed as option-space.

Click Change All, then OK the warning and the report of the number of changes.

Find/Change will replace all of the occurences of (space)and(space) with (option-space)and(option-space), leaving the entry looking much like it did before. But the FIND() function, which is looking for standard 'space' characters will ignore these,and correctly separate the honorific (Mr. and Mrs.) from the rest of the name.

Regards,
Barry

[rglocke]

Thank you Barry,

I did not expect such a prompt solution. It works great.

This Appleworks Help process is wonderful.

Thanks again!

Robert