Split Full Names Into First and Last

What it does

You have a column of full names. You need First Name and Last Name in separate columns for a mail merge, an email tool, a CRM upload, or just to sort by surname. Excel's Text to Columns can split on a delimiter but treats every space the same way, so "Ana Maria Lopez" becomes three columns instead of First "Ana Maria" / Last "Lopez". The macro asks which format your names are in (First Last or Last, First), then writes First Name and Last Name into the two columns to the right of your selection, putting any middle names into the First Name field where they belong.

Real-world example

Picture a substitute teacher placement coordinator at a regional school district. Each Monday morning the district HR system spits out a CSV of substitutes who confirmed availability that week, and her job is to upload that list into the dispatch tool that calls people. The dispatch tool requires First Name and Last Name in separate columns. The CSV gives her one column called Full Name with entries like Maria Sanchez, Robert Lee Johnson Jr (sigh), and Patel, Anika (because some HR records use the alternate format).

Her current workflow: Text to Columns with space as delimiter for the first-last entries, manual fix for the comma-separated ones, manual fix for the middle-name and Jr/Sr entries. About 6 minutes per week for 80 names, with two or three errors per week that the dispatch tool flags as duplicates because she got the first/last wrong.

With this macro she selects the name column, runs the tool, picks 1 for "First Last" format, and 65 of the 80 names split into First and Last cleanly. The macro handles "Maria Sanchez" perfectly and treats "Robert Lee Johnson" as First "Robert Lee" and Last "Johnson", which is right. She still hand-fixes the 15 comma-separated entries (Patel, Anika style) by either re-running the macro on just those rows with format 2, or by editing manually. Total elapsed: about 90 seconds.

On a typical 80-name weekly upload, the macro takes the work from 6 minutes to under 2 minutes, plus eliminates the most common error (treating "Maria Sanchez" as last "Sanchez" but treating "Ana Maria Lopez" as last "Maria"). Across the school year that's about 2.5 hours back.

How to use

First time? See How to run our tools for the 30-second setup.

1. Make sure the two columns to the right of your name column are empty. The macro writes First Name to column +1 and Last Name to column +2.
2. Select the single column of full names.
3. Press Alt + F8 (Windows) or Option + F8 (Mac), pick SplitFullNames, click Run.
4. Type 1 for "First Last" format or 2 for "Last, First" format.
5. The macro fills First Name and Last Name in the two adjacent columns and reports a count.

FAQ

How does it handle middle names?

In "First Last" format, anything before the last word becomes the First Name. So "Ana Maria Lopez" gives First "Ana Maria" and Last "Lopez". "Robert Lee Johnson" gives First "Robert Lee" and Last "Johnson". This matches what most CRM systems want when they only have two name fields. If you need a separate Middle Name column, this macro is the wrong tool; you'd want a more complex parser.

What about "Jr.", "Sr.", and "III" suffixes?

Suffixes are a known limitation. "Robert Lee Johnson Jr" gives First "Robert Lee Johnson" and Last "Jr", which is wrong. About 1% of typical name lists include a suffix, so the failure rate is real but small. After running the macro, sort by Last Name and you'll see all the "Jr", "Sr", "III" entries clustered together; fix them by hand. We may add suffix detection in a future version.

Does it work for names with hyphens or apostrophes?

Yes. Hyphenated names like "Smith-Jones" or apostrophe names like "O'Brien" are treated as a single word because the split is on whitespace, not punctuation. So "Maria Smith-Jones" gives First "Maria" and Last "Smith-Jones", which is correct.

What if the cell only has one word, like "Madonna"?

Single-word entries become First "" and Last "Madonna" in Last, First mode. In "First Last" mode, the same input gives First "Madonna" and Last "". Either way, no data is lost; you can spot the single-word entries afterward by filtering for blank in either output column.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. The macro only writes to the two columns to the right of your selection, so undo cleanly removes the output without touching your source. As always, work on a copy of your data if it's the only one. The macro runs entirely offline. Your data never leaves your machine.