How to Separate First and Last Names in Google Sheets

Customer contact lists, event registrations, and corporate rosters frequently arrive with full names combined in a single cell or column. When preparing mail merges, synchronising CRM systems, or sorting client records alphabetically by surname, separating full names into dedicated “First Name” and “Last Name” columns becomes an essential data cleaning task.

Google Sheets offers both an automated point-and-click splitting utility and flexible dynamic formulas to parse names effortlessly without manual re-typing.

Method 1: Splitting Names Using the “Split Text to Columns” Tool

If you prefer a fast, one-time static separation that does not rely on active spreadsheet formulas:

1. Open your spreadsheet in Google Sheets and select the column containing your full names.
2. Ensure there is at least one empty column directly to the right of your name column, as the splitting tool will overwrite adjacent cells.
3. Click Data in the top menu bar.
4. Select Split text to columns from the drop-down menu.
5. A small separator dialogue box will appear near the bottom-left corner of your selection. Click the Separator drop-down and choose Space (or keep Detect automatically).

Google Sheets will instantly divide the text string across neighbouring columns at each space character.

Method 2: Extracting First and Last Names Using the SPLIT Formula

If new names are added to your spreadsheet regularly and you need the split to calculate dynamically:

1. Click the cell where you want the first name to appear (for example, cell B2 next to full name A2).
2. Enter the following formula:

   =SPLIT(A2, " ")

3. Press Enter. Google Sheets automatically spills the first name into column B and the surname into column C.
4. Drag the blue fill handle down the column to apply the formula across your entire contact list.

Method 3: Handling Middle Names and Multi-Part Surnames

When lists contain three-part names (such as “Mary Jane Watson” or “Arthur Conan Doyle”), basic space splitting creates three separate columns. To strictly extract the first word as the First Name and all remaining words as the Last Name:

• First Name Formula (in B2):

  =REGEXEXTRACT(A2, "^(\S+)")

• Last Name Formula (in C2):

  =REGEXEXTRACT(A2, "\s+(.*)$")

These regular expression formulas ensure that whether a contact has a single surname or a double-barrelled name with middle initials, the first given name stays isolated in column B while the remainder populates column C cleanly.