Splitting Full Name into First and Last Name

When preparing a CSV file for import, especially in systems like NetSuite, Salesforce, or other CRMs, data often needs to be structured correctly. One common issue is having a full name in one column while requiring separate First Name and Last Name columns.

Instead of manually splitting names, an advanced Excel formula can automatically extract first and last names.

Assuming Column A contains full names (e.g., “John Michael Doe”), use these formulas:

=LEFT(A2, FIND(” “, A2)-1)

How It Works:

• FIND(" ", A2) locates the first space.
• LEFT(A2, FIND(" ", A2)-1) extracts everything before the first space.

Extract Last Name (Including Middle Name If Present):

=RIGHT(A2, LEN(A2)-FIND(” “, A2))

How It Works:

• FIND(" ", A2) finds the first space.
• LEN(A2)-FIND(" ", A2) calculates how many characters remain after the first space.
• RIGHT(A2, ...) extracts everything after the first space.