As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Split First and Last Name in Excel: Step-by-Step Guide

If you have a list of names in Excel that you want to split into first and last names, you can achieve this using formulas like LEFT, RIGHT, and MID, or by using the Text to Columns feature. Here's a step-by-step guide.

Using the LEFT Formula:

1. Enter the LEFT Formula:

  • In a blank cell, type the following formula:
  • =LEFT(cell, number_of_characters)

2. Select the Cell Containing the Name:

  • Replace "cell" with the actual cell reference (e.g., A2) containing the full name.

3. Enter the Number of Characters to Extract:

  • Specify the number of characters for the first name.

4. Complete the Formula:

  • The formula might look like this: =LEFT(A2,5).

5. Press Enter to Split the Name:

  • Press Enter, and the first name will be extracted.

Using the RIGHT Formula:

1. Enter the RIGHT Formula:

  • In a blank cell, type the following formula:
  • =RIGHT(cell, number_of_characters)

2. Select the Cell Containing the Name:

  • Replace "cell" with the actual cell reference (e.g., A2) containing the full name.

3. Enter the Number of Characters to Extract:

  • Specify the number of characters for the last name.

4. Complete the Formula:

  • The formula might look like this: =RIGHT(A2,5).

5. Press Enter to Split the Name:

  • Press Enter, and the last name will be extracted.

Using the MID Formula:

1. Enter the MID Formula:

  • In a blank cell, type the following formula:
  • =MID(cell, start_number, number_of_characters)

2. Select the Cell Containing the Name:

  • Replace "cell" with the actual cell reference (e.g., A2) containing the full name.

3. Enter the Start Number:

  • Specify the starting character position (usually 1).

4. Enter the Number of Characters to Extract:

  • Specify the number of characters for the first name.

5. Complete the Formula:

  • The formula might look like this: =MID(A2,1,5).

6. Press Enter to Split the Name:

  • Press Enter, and the first name will be extracted.

Using Text to Columns:

1. Select the Cells Containing the Names:

  • Highlight the cells containing the full names.

2. Go to Data > Text to Columns:

  • Navigate to the Data tab, click on Text to Columns.

3. Choose the Delimited Option:

  • Select the Delimited option and click Next.

4. Choose the Space Delimiter:

  • Choose the Space delimiter and click Next.

5. Choose the Formatting Options:

  • The default options are usually fine; click Finish.

6. Split the Names:

  • The names will be split into two columns.

By following these steps, you can efficiently split first and last names in Excel using different formulas or the Text to Columns feature.