Excel

Splitting first and last names in Excel using 3 methods

splitting first and last names in excel

Suppose you have a list of names in your data records separated by a space, now want to separate first and last names into two columns.

Microsoft365 for Business

Microsoft Excel allows to spilled a single cell or multiple cells. In our previous article, we try to explain how to split a single cell in half in Excel. In the guides below, we’ll converse about three effective methods for splitting first and last names in excel into two columns.

We’ll be using the most recent version of Microsoft Excel 2019 for this article. However, the methods work on all Excel versions.

Method 1. Splitting names using the Text to Columns function

If your data set contains a list of names where the respective first and last names are in one cell, you can use the text to columns wizard to separate them into separate cells if required.

Follow these steps to splitting names using the text to columns tool:

1. Open the file containing the list of names you want to split.

2. Select all the names in the “Full Name” column.

3. Switch to the “Data” tab and click on the “Text to Columns” option under the “Data Tools” category.

text to columns

4. Convert Text to Columns Wizard dialog box will appear. Here have three steps, each of which requires you to make changes.

5. In step 1 of 3: Select “Delimited” (this allows you to use a space between the first and last names as a separator). Click “next“.

Select Delimited in the Text to Columns Wizard-1

6. In Step 2 of 3: Uncheck all Delimiters first and then check “Space” only. Click “next”.

Check the space option in Text to Columns Wizard-2

7. In step 3 of 3: Select the “Destination” cell, where the first name appears after splitting. For our example, we select column E2

Change the destination cell in Text to Columns Wizard-3

8. And finally, click “Finish“.

Now, we were able to successfully split the first and last names from a single cell into two separate cells, as shown below.

split the first and last names

PRO Note
Text to columns will overwrite the existing column if you don’t specify a destination cell.

Method 2. Use Formulas for splitting first and last names in Excel spreadsheet

A different approach to splitting first and last names in Excel is using formulas.

If you change the full name, the change will automatically be reflex in the split first and last name cells as well.

You can split your full names cell fairly easily using the following formula:
Cell B2: First Name [space] Last Name

Formula to get the First Name in Cell E2

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

Another formula to split the first name


=LEFT(B2,SEARCH(” “,B2)-1)
split the first names formulas

Formula to get the Last Name in Cell F2

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

Another formula to split the last name


=RIGHT(B2,LEN(B2)-SEARCH(” “,B2))
split the last names formulas

Do not forget to enter the space between the quotation marks.

If you want to put them back together, for example, if you want to get the first and last names separately, but want them to be in one cell, use the following formula:

=E2 &” “& F2
combine multiple columns

This allows you to reassemble the names. The space between the quotation marks is important here. So that there is a space between the first and last name.

Method 3. Separate first and last names Using Find and Replace

You can separate first and last names with Excel’s Find and Replace feature. This one is weird, but it works and may be faster than the other options. Let’s see the below process to splitting names using it.

Get the First Name using Find and Replace

1. Select all the name datasets (B2:B9 for this example) and copy them using the “Ctrl+C” keyboard shortcut.

2. Select the destination cell and paste them. (Ctrl+keyboard shortcut).

copy and paste the names to the destination columns

3. Select your pasted all full name and stay on the Home tab. Click the “Find & Select” in the Editing group.

4. Click on “Replace“. A new dialog box will appear.

click the replace option

5. Enter the following into the “Find and Replace” dialog box:

  1. Find what: * (Type a space ( ), and then an asterisk *)
  2. Replace with: Leave this field blank
Type Find What and Replace with values in the Find and Replace dialog box

6. And then click “Replace All“.

click on replace all

Result

split first names

Get the Last Name using Find and Replace

1. Select all the name datasets (B2:B9 for this example) and then copy and paste them to the destination cell.

2. Click the “Find & Select” button in the Editing group on the Home tab.

3. Click on “Replace“. A new dialog box will appear.

4. Enter the following into the “Find and Replace” dialog box:

  1. Find what: * (Type an asterisk *, and then a space ( ) character).
  2. Replace with: Leave this field blank

 5. And then click “Replace All“.

find and replace dialog box

You Might Also Like

Microsoft

No Comments

Leave a Reply

back to top