Google Sheets has a number of abilities to make your everyday life a lot easier. Most people need to break a name into first name and last name if there is a sheet containing the full name. If a sheet is collecting data from a form that collects the first and the last name in two different text boxes, you might need to merge the cells to get the full name. 

While you can always merge the first and last name or break the name into first and last names manually, it can become increasingly difficult based on the number of entries within the sheet. Today, I will talk about, how you can merge the first names and last names or break a name into first, middle, and last names. This can be useful in several situations, no matter which industry you are in.

Let’s get started with how to break a name into parts or combine the parts of a name into one cell.

Combining first name and last name

To combine first and last names, we will use the CONCAT formula in Google Sheets, but we have to use it with a twist so that it looks like a real name that can hence be used for other activities.

Combining first name and last name

In the example below, we have the first name in column A and the last name in column B. We will have the combined name in column D.

So, the formula in cell D1 will go as given below.

=concat(concat(A1," "),B1)

Now, simply copy the same formula in the next cells of the column using the small dot on the down-right corner of the cell.

Why use the nested CONCAT function?

CONCAT is a function that can combine only two entries into one. So, if we use the CONCAT function to directly link entries within two cells, we will get the full name without any space in between.

So, we are using the first CONCAT function to link the first name with a space, and then link the result of the first CONCAT function, which has the first name and the space, with the last name.

Combining first name, middle name (if that exists), and last name

In the below example, we have the first name in column A, the middle name that is optional, in column B, and the last name in column C. We want to get the result in column D.

So, in the below example, the formula in cell D1 will go as follows.

=if(B1="",(concat(concat(A1," "),C1)),(concat(concat(concat(concat(A1," "),B1)," "),C1)))

In a similar way, copy the formula in the subsequent cells. This formula will generate the full name, no matter whether there is a middle name or not.

If there is no middle name, however, by mistake, the last name is in the cell, which is supposed to have the middle name, this function is going to work flawlessly, as well.

Why use IF, and the CONCAT function 4 times?

We could simply use the nested CONCAT function 4 times, but that will put two spaces in the final full name if there is no middle name.

Using the IF function here invokes linking the first name and the last name, if there is no middle name, or linking the first name, middle name, and last name if a middle name exists.

The first part of the IF function, i.e. the TRUE case if there is no middle name, has been explained in the last section. Whereas, if there is a middle name, i.e. in the FALSE case, of the IF function, we are using the CONCAT function 4 times. Here’s why.

1st CONCAT: Links first name with a space

2nd CONCAT: Links the result of the first CONCAT with the middle name

3rd CONCAT: Links the combined result of the 2nd CONCAT with the space between the middle name and last name.

4th CONCAT: Links the combined result of the first 3 CONCAT functions with the last name.

Hope the whole thing was understandable for you.

How to split a full name into the first, middle (if exists), and last name

If you have a sheet with the full name, you can split the full name into first, middle, and last names. In the case of splitting the full name, the formula is not going to be different, no matter whether there is a middle name or not.

In the below example, we have the full name in A1, and we want to split the name into first name, middle name, and last name, in the subsequent cells of the same row, starting with the first name in cell B1.

The formula in cell B1 will go as follows.

=SPLIT(A1," ")

This will automatically place the first name in cell B1, the middle name or the last name in C1, and so on.

While you can also use the ‘Split text to columns’ option within the ‘Data’ menu, there is a major problem, if you are dealing with a huge set of data.

This function will split the name, place the first name in the current cell, and the middle and the last names in the subsequent cells within the same row. So, the full name will not be retained in the original cell, and if there is some data in the subsequent cells of the row, all that data will be replaced with the middle name and the last name. So, it is better not to go with this way of splitting a name in Google Sheets.

So, that’s basically how to merge first name, and last name, or break the full name to first and last name in Google Sheets. Do you have any questions regarding the topic? Feel free to comment on the same below.