Extracting Names

[franknove]

I have a file which can contain one name such as John Smith or two names such as John Smith & Betty Brown.

When there are two names they are separated by an "&".

I would like to extract name 1 into two fields Contact1Name and Contact1Surname, and if a second name exists extract it into Contact2Name and Contact2Surname.

To complicate matters, sometimes the surname is only included once in the data at the end as - "John & Betty Smith"

Is it possible for the calculation to take into account that there are sometimes 4 names and other times 3?

Any assistance would be appreciated.

[Lee Smith]

You might have more problems with consistency than what you are seeing.

When you only have one Field for names, entry is left open for interpretation. Many threads have been written here with similar problems. The best way to control entry, is to be percise with the fields to be used, and what information is required.

One field will always cause you problem, as it can be like you see in your example. How about other veriations such as:

Mr. Jones,

Mr. and Mrs. Jones

Mr Tom Jones, & Lilly Jones

Tom W. Jones and Lilly White-Jones

etc.

It is better to have a field for each component of the name

Title, First Name, Middle Int, Last Name

if you have more than one name for a Record, then give each potential name their own fields.

check the linked file to this thread Here

If you review your records, and you only have names for the two examples given, let me know and I'll post a quick fix for separating them into separate fields.

HTH

Lee

[franknove]

Hello Lee,

Thanks for your help.

I am trying to clean up our Accounting System Database to export the data to a CRM package. The Accounting System has two lines of data for names, and basically over the years the information has been typed in in various permutations as per the ones you outlined in your previous response. Sometimes we commence with "Mr" "Mrs" etc, and other times we only use initials where the full first name was not obtained.

I know that it is going to be impossible (at least I think it is?) to cover all the permutations, however I have ascertained that the two most common forms of data imput are those I stated in my initial query.

If we can find a solution for the two outlined, I can then go through and gradually clean up the remainder.

Thanks again.

Frank

[Lee Smith]

Hi Frank,

Okay, I have made a little file for you to test your data, and see what will happen with the different formats.

I remind you to download the file I linked in the previous post "Link" that is a file by Lynne Bradford.

The first Field equals your current field were all the names are combined.

The next 2 fields split the original field

the next 4 (2 for each) split the 2 fields (contact one and two) in to separate first and last names.

The last two are CFs that you can use if you have Advance, or access to Advance (Developer for version 7)

HTH

Lee

franknove.fp7.zip

Edited December 15, 2007 by Guest