TidyRow

Nothing uploaded

How to separate first and last name in Excel

Excel has three ways to do it, each with catches: extra spaces, middle names and ALL CAPS names trip them up. Here’s how each one works, plus a way to skip the formulas entirely.

Show me how to do it myself or use the tool below.

Example

dr. sarah TAN

Sarah | Tan

Add your file

A CSV or Excel file exported from your store, CRM, email tool or spreadsheet.

Want proof nothing is uploaded? Once this page has loaded, turn off your Wi-Fi. It still works.

Check what we fixed

Names, phone numbers, emails and duplicates are fixed for you.

Download your clean file

Method 1: Text to Columns

  1. Insert two empty columns to the right of your name column.
  2. Select the name column, then go to Data → Text to Columns.
  3. Choose Delimited, tick Space, and click Finish.

Catch: “Mary Ann Lee” becomes three columns, and double spaces create empty cells. Capitals and titles like “Dr.” aren’t fixed.

Method 2: Flash Fill

  1. Next to the first name, type how the first name should look (for example “Sarah”).
  2. Move to the cell below and press Ctrl + E. Excel fills in the rest by copying your pattern.
  3. Repeat in the next column for last names.

Catch: Flash Fill guesses. Check the results, especially for middle names, titles and double surnames.

Method 3: Formulas

With the full name in cell A2:

First name
=PROPER(LEFT(TRIM(A2), FIND(" ", TRIM(A2)) - 1))
Last name
=PROPER(TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100)))
Excel 365 shortcut
=TEXTBEFORE(TRIM(A2), " ") and =TEXTAFTER(TRIM(A2), " ", -1)

TRIM removes extra spaces and PROPER fixes capitals. Titles like “Dr.” still need removing by hand, and single-word names show an error.

Or skip the formulas

  1. Save your sheet as .xlsx or .csv (it already is if you’re in Excel).
  2. Drop it onto the box above. TidyRow finds the name column on its own.
  3. Download a file with clean First Name and Last Name columns.
  • Extra spaces and ALL CAPS fixed: “JOHN doe” → John | Doe
  • Titles removed: “Dr. Sarah Tan” → Sarah | Tan
  • Middle names kept with the first name, or in their own column
  • “Tan, Sarah” order and family-name-first names handled
  • Free for up to 100 rows, and your file is never uploaded

Frequently asked questions

What’s the quickest way to separate names in Excel?

For a short list, Flash Fill (Ctrl + E) is quickest. For a long or messy list with extra spaces, titles or ALL CAPS, dropping the file into TidyRow is faster because it fixes those at the same time.

How do I handle middle names?

Excel’s last-name formula above takes the last word, so “Mary Ann Lee” gives Lee and you can take everything before it as the first name. TidyRow does this automatically, or can put middle names in their own column.

Does TidyRow work with Excel files?

Yes. Drop in an .xlsx file directly. You get a clean CSV back, which opens in Excel and imports into any CRM.

Other clean-ups