Parsing a compound name

[john9210]

I’m using the following formula to count the number of people in a compound name. If[WordCount(name)>2,2,1]. It gives the following results:

Name                          Result

Smith, Jane                    1

Smith, Jane and Joe       2

Smith, Jane Agnes          2

The last result is incorrect. It should be 1 since Jane Agnes is a first name.

What would be a correct formula to use?

[comment]

I am afraid these examples are not sufficient to determine what rules your data follows. I am guessing that "Smith-Jones, Adam" would also fail your test. The question is: what is unique to an entry of two (or more?) people? Will they always have the same last name? Will there always be an " and " separating their first names?

[john9210]

What I'm trying to do is determine the number of people a name represents. In a name, both persons always have the same last name. There is always be an "and" separating the last and first names.

Generally, names are entered as" Jones, Mary" for one person, "Jones, Mary and John" for two persons. The problem arises when the first name is made up of two parts, a relatively rare occasion, but it does happen.

[Lee Smith]

Hi John,

When asking for help on parsing text, it is always best to provide actual examples of the data. There are so many variables that can effect the results.

In fact, a copy of the file is usually helpful.

Lee

[comment]

If there's always an " and " separating the names, then do a PatternCount() for it. Make sure to include the surrounding spaces to avoid false positives with names like Brandon.