How to Separate First and Last Name in Google Sheets

Sanskar Tiwari

Author: Sanskar Tiwari

Founder at SheetAI

Published

Easily separate full names into first and last names in Google Sheets using simple tools and formulas

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

notion image
  • 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).
notion image
  • 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.
notion image
  • 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.
    notion image
    • 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.

    Tired of spending hours cleaning data or writing complex formulas? Here’s a smarter solution: SheetAI.app

    Whether you're managing sales reports, crafting marketing content, or analyzing feedback, working in spreadsheets can be time-consuming and repetitive. But what if you could automate these tasks and enhance your productivity directly within Google Sheets? That's exactly what SheetAI offers.
    notion image
    SheetAI is an AI-powered add-on for Google Sheets that brings advanced capabilities right into your spreadsheet. Simply describe what you want in plain English, and SheetAI will execute tasks such as generating formulas, cleaning data, summarizing text, and even creating images. (SheetAI Documentation - AI-Powered Functions for Google Sheets)
    Here’s what you’ll love about SheetAI:
    • Natural Language Commands: Use plain English to perform complex tasks.
    • AI-Powered Functions: Leverage functions like =SHEETAI() and =SHEETAI_RANGE() for dynamic content generation.
    • Data Cleaning & Analysis: Automate data sanitization, summarization, and categorization.
    • Content Generation: Create blog ideas, product descriptions, and personalized messages effortlessly.
    • Image Creation: Generate images directly within your spreadsheet using =SHEETAI_IMAGE().
    • Formula Generation: Convert textual descriptions into accurate Google Sheets formulas.
    • Integration with AI Models: Supports GPT-3, GPT-4, Claude, and Gemini for versatile AI capabilities.
    Perfect for marketers, analysts, educators, and anyone looking to enhance their spreadsheet workflows.
    Ready to revolutionize your Google Sheets experience? Install SheetAI now and start automating your tasks with AI!

    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.

    Share on socials