How to Separate First and Last Name in Google Sheets

Managing names in a spreadsheet becomes much easier when first and last names are in separate columns.

Whether you’re organizing contact lists, generating certificates, or sorting names alphabetically, splitting full names in Google Sheets can save a lot of time. Luckily, Google Sheets offers built-in tools and formulas that make this process effortless.

In this step-by-step guide, we’ll show you how to separate first and last names using the Split Text to Columns feature and simple functions like SPLIT(). You’ll be organizing your data more effectively in no time.

Step-by-Step: How to Separate First and Last Name in Google Sheets

Step 1: Open Your Google Sheet

Visit sheets.google.com and open your spreadsheet.

Make sure the full names are in one column (e.g., Column A).

Step 2: Use Split Text to Columns

Select the cells with full names (e.g., A2:A10).

Go to the top menu and click Data > Split text to columns.

A separator menu will appear. Choose Space if names are separated by a space.

First and last names will now appear in separate columns (e.g., Column A and B).

Step 3: Use the SPLIT Function (Formula Method)

Click on a new column (e.g., B2) and enter:

Press Enter. This will split the first and last name based on the space.

Drag the fill handle down to apply to the rest of the column.

Step 4: Handle Middle Names (Optional)

If names have middle names, SPLIT() will divide them into separate columns as well.

You may need to use =INDEX(SPLIT(A2, " "), 1) for first name and =INDEX(SPLIT(A2, " "), 2) or 3 for last/middle name accordingly.

Conclusion

Separating first and last names in Google Sheets is quick with the built-in Split to Columns feature or the SPLIT() formula.

These tools allow for cleaner data organization, especially when working with large contact lists or customer records.

With just a few clicks or a simple formula, you can keep your spreadsheets well-structured and easier to manage.

FAQs can take you forward!

Will this work if there’s a middle name?

Yes, but the name will be split into multiple columns. You may need to use formulas like INDEX() to extract specific parts.

Can I undo the split?

Yes, simply press Ctrl + Z or use the Undo button.

How do I separate names with commas?

Use the Split to Columns option and select Comma as the separator.

Will this affect the original data?

No, it only rearranges how the data is displayed. Original data stays in place unless overwritten.

How to Separate First and Last Name in Google Sheets