How to separate first and last name in Google Sheets
Google Sheets can split names with a menu option or a formula. Both work for tidy lists; messy ones need extra steps. Here’s how, and how to skip it.
Show me how to do it myself or use the tool below.
MARY ann LEE
Mary Ann | Lee
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: Split text to columns
- Make sure the columns to the right of your names are empty.
- Select the name column, then go to Data → Split text to columns.
- In the separator box that appears, choose Space.
Catch: middle names spill into a third column, and capitals and titles aren’t fixed.
Method 2: The SPLIT formula
With the full name in cell A2:
- Split into columns
=SPLIT(PROPER(TRIM(A2)), " ")- First name only
=INDEX(SPLIT(PROPER(TRIM(A2)), " "), 1)- Last name only
=INDEX(SPLIT(PROPER(TRIM(A2)), " "), COUNTA(SPLIT(A2, " ")))
TRIM removes extra spaces and PROPER fixes capitals. Titles like “Mr” or “Dr.” still end up as the first name.
Or skip the formulas
- In Google Sheets, go to File → Download → Comma-separated values (.csv).
- Drop the file onto the box above.
- Download the cleaned file and import it back with File → Import.
- Capitals, extra spaces and titles fixed in one go
- Middle names kept with the first name, or in their own column
- Phone numbers and emails cleaned at the same time
- Free for up to 100 rows, and your file is never uploaded
Frequently asked questions
Why does SPLIT give me three columns?
Because the name has a middle name, or a title like “Dr.”. Use the first-name and last-name formulas above to pick just the first and last word, or let TidyRow handle it.
Can TidyRow read my Google Sheet directly?
Not directly. Download it as a CSV first (File → Download → .csv). Keeping it a file is what lets TidyRow clean it without uploading anything.