Split full name into first and last, for any contact list

Use Data > Text to Columns > Delimited, tick Space, pick a destination column and click Finish. In Excel for Microsoft 365 or Excel 2024, formulas do it without touching the original:

• First name: =TEXTBEFORE(A2," ")
• Last name: =TEXTAFTER(A2," ",-1)

The -1 tells Excel to search from the end, so it takes the last word.

Example: “Mary Ann Lopez” gives “Mary” and “Lopez”. “Ann” is lost, and “Lopez Jr.” would come out as “Jr.”, which is why the tool above handles suffixes and middle names for you.

Select the column, then Data > Split text to columns and set the separator to space. As a formula, =SPLIT(A2," ") puts each word in its own column.

Example: “Dr. Robert J. Alvarez” becomes four columns: “Dr.”, “Robert”, “J.” and “Alvarez”. Any name with a title or middle initial needs fixing by hand afterward.

Split at the comma: the part before it is the last name, the part after it is the first name. In Excel 365:

• Last name: =TEXTBEFORE(A2,",")
• First name: =TRIM(TEXTAFTER(A2,","))

In Google Sheets, =SPLIT(A2,", ") splits on the comma and the space and drops the empty piece between them.

Example: “Smith, John” gives “Smith” and “John”. “Smith, John A.” gives “Smith”, “John” and “A.” in Sheets. The tool spots this format row by row, so a list that mixes both formats still splits correctly.

It reads each name in this order:

• Titles at the start (Mr., Mrs., Dr., Rev.) go to Prefix.
• Suffixes at the end (Jr., Sr., III, PhD, MD, CPA, Esq.) go to Suffix.
• A comma with a first name after it means “Last, First”.
• Last-name particles stay with the last name: “van der Berg”, “de la Cruz”, “St. James”.
• First word is the first name, last word is the last name, anything between is the middle.

Nicknames in quotes or brackets, like “Lin” or (Bob), are left out and noted.

Company names (LLC, Inc, Plumbing, Services) are flagged and left whole. Couples, single words, email addresses, names of four or more words and two-word names that could be a business, like “Dana Sales”, are flagged for you to check.

Mailchimp’s *|FNAME|* and *|LNAME|* merge tags insert each contact’s first and last name, so a wrong split shows up in every greeting.

• Filter the download by Status = Check and fix those rows first.
• Put flagged companies in the company field, not the last name.
• Choose Middle name: with the first name if your list only has two name fields.
• Keep the original full-name column until the import looks right.

For a list with repeats, run the HubSpot duplicate contact finder or the duplicate customer finder next.