Separate 'full name' into four columns

NadiaM100223

Hi all,

I've read several posts on this topic and I still can't figure it out. I have a 'Full Name' column that I want to separate into four columns (First Name, Middle Name, Last name, Suffix). Can someone please provide the formula for this?

Thank you!

Paul Newcome

Unfortunately the only thing we can consistently populate would be the first name. Anything else wouldn't be reliable because of the inconsistency.

If you could consistently have something populated for those fields that may not have data (maybe a default value in the form), we could work with that, but otherwise we are unfortunately not able to do much.

Paul Newcome

What structure are the full names in? Are you able to provide some sample data?

NadiaM100223

Yes, the full names are separated with a space. An example would be:

Full Name: John Adam Doe Jr.

However, some last names include a hyphen. An example would be:

Full Name: Jane Rachel Doe-Smith

Some full names do not include a middle name. An example would be:

Full Name: John Doe

In some cases, the middle name entered is only an initial such as "John A. Doe"

Paul Newcome

What are the chances there could be a suffix but no middle name? "John Doe Jr." for example. Will the suffix always end with a period and be 3 characters long (including the period)?

NadiaM100223

There may be names that have a suffix and no middle name. Likewise, the suffix does not always end with a period (such as John Doe IV). In some cases, the form submissions will include "N/A" or "None" as a suffix or middle name.

Paul Newcome

Unfortunately the only thing we can consistently populate would be the first name. Anything else wouldn't be reliable because of the inconsistency.

If you could consistently have something populated for those fields that may not have data (maybe a default value in the form), we could work with that, but otherwise we are unfortunately not able to do much.