How can I split a column of first name and last name in to two separate columns in Excel

To ensure Excel’s Text to Columns feature only splits at the first space, you can use a combination of Excel functions to achieve this. Here’s a step-by-step guide:

  1. Insert “Helper” Columns: Add two new columns next to your full names column.
  2. Extract First Name:
    • In the first helper column, use the formula:
      =LEFT(A1, FIND(" ", A1) - 1)
      This formula finds the first space and extracts the text before it.
  3. Extract Last Name:
    • In the second helper column, use the formula:
      =MID(A1, FIND(" ", A1) + 1, LEN(A1))
      This formula finds the first space and extracts the text after it.
  4. Copy and Paste Values:
    • Copy the results from the helper columns and paste them as values to replace the original full names column.

This method ensures that only the first space is used to split the names, regardless of any additional spaces in the text.

Leave a Reply