How To Separate First and Last Names in Google Sheets

If you have a roster full of names, it can be useful to split them up by first and last name. You might need to create a list of your clients’ or employees’ last names, and first names are useful for greetings and messages.

There are many different ways to split a column of full names into separate columns in Google Sheets. Let’s look into two simple and efficient methods you can use.

Use the Split Text into Columns Tool

Here is the most straightforward way to split full names into different columns using Google Sheets tools.

1. Create a copy of the cells in the column with the full names. The Split Text into Columns tool will alter the names found inside the column you split. If you want to keep the initial names intact, you should apply the add-on to a copied version of the original column.
   
2. Paste the names in the topmost cell within the column where you want them to split.
   
3. Select the newly pasted cells, select “Data” from the top menu, and then click on “Split text to columns.”
   
4. The previous step automatically splits up your data, but you have to choose a Separator before the split shows. In the Separator dropdown menu, select “Space,” which splits the names between spaces to separate the first and last names into separate cells.
   
5. After splitting the names, your cells should look like the following image:
   

If every name on your roster consists of just a first name and the last name, the method above splits them neatly into two.

For separating names with middle initials or spelled-out middle names, follow the same procedure as above, except it will split all three names into adjacent cells.

Note that mixing first and last name cells with ones that include middle names or initials will cause improper column structures, as ALSO shown above.

Separating Full Names with Two-Worded First Names, Hyphens, or Apostrophes

Using commas (Last Name, First Name) offers more flexibility when it comes to separating names in Google sheets. If commas separate the full names, choose the “Comma” Separator instead of the “Space” one.

This scenario is handy when you need to correctly display specific names, such as “Oswald, Betty Grace,” or “Riley, Mary Kate.” It also works for names with hyphens and apostrophes. As long as you have commas between each part of the names, the “Comma” Separator will work perfectly.

Use a Google Sheets Add-On to Separate Full Names

If you need middle names that DON’T mess up cell alignment, installing the Split Names add-on might be more convenient. The extension isn’t free, but it’s relatively affordable, and it does include a 30-day trial period. Follow these steps to install Split Names.

1. Click on “Add-ons” at the top of the page, and then click “Get add-ons”
   
2. In the Search Bar, Type “split names,” and then click on the add-on to open its page
   
3. Click “Install” and follow the prompts.
   

How to Use Split Names Add-On in Google Sheets

1. Select the cells with full names within the column, and then select “Add-ons -> Split Names -> Start”
   
2. Check and uncheck name options:
   First name
   Middle name
   Last name
   Salutation/title
   Name suffix/post-nominal letters
   My column has a header
   
3. Select “Split” and your results should be similar to the image below
   

When you select “Split,” the add-on will create new columns and automatically add headers to each one, unless you unchecked the “My column has a header” option.

It’s clear that this add-on is very efficient and separates full names with ease.

After you click on “Split,” you will get three separate columns. If there are multiple middle names, they all go into the Middle Name column.

A Few Words on Honorifics, Suffixes, and Complex Last Names

The Google Sheets Split Names Add-On by Ablebits is flexible and covers many different name types. You can also check the Suffixes/post-nominal letters, such as Jr./Sr. and post-nominal titles such as Esq. or Ph.D.

If a person’s full name doesn’t contain a title or a suffix, their field will remain blank.

Other methods make it difficult to split apart complex last names, but this add-on recognizes that prefixes like “de” or “von” are a part of the last name.

Regardless of the add-on’s amazing benefits, it’s not infallible. For example, the tool splits famous physicist Van der Graaff’s last name into middle name Van and last name der Graaff.

In closing, using the Split Names add-on in Google Sheets is more convenient, and it is much better at handling names, prefixes, and suffixes. The downside is that the add-on takes some time to generate new columns for big lists. Also, some users prefer not to rely on add-ons at all, especially if it’s necessary to pay for them.

If you decide to use Split Text into Columns, you will have quicker results. Every part of the full name will go into different columns. This method is quicker, but it can be a hassle to round up all the last names or middle names.

You may also prefer to use formulas instead of these methods. Whichever approach you go for, Google Sheets makes this process quite simple.