How to Combine First and Last Name in Google Sheets

Hi, everyone. In this article, we are going to learn how to combine first and last names in Google Sheets. We work with diverse data in Google Sheets and sometimes we import CSV files into Sheets to work with them, but in these cases, we have a problem organizing our data, the first problem that appears is that we have our full name broken down with commas and now we have it to combine the first, middle and last name to make a full name, this can be very tedious if we have 1000 names or even 100 names, so is there any method in Google sheets to combine first and last name in automatically? Well, yes there are so many methods to do this automatically and very quickly without wasting time and leaving errors, so today we are going to learn how to combine first and last names in Google sheets.

Why Combine text is important in Google Sheets?

As I have told you that we often have broken data in our Sheets file, there could be many more entities like first and last name, and address broken into two columns or separated by commas, contact number separated with country and city code, and so on, we have already learned how to split first and last name in Google Sheets.

So reserving that logic can help us combine first and last name, and there is where we need to learn how to combine first and last name in Google sheets, we have various method for doing this, we will see multiple methods using formulas, functions, etc. You can use what fits your requirement best and you find it comfortable to use. Let’s go to the step-by-step procedure to closely look at how it works.

How to Combine First and Last Name in Google Sheets

So first, we will see the first method which is a direct formula method, then we will see some quick functions, and then we will see some manual but easy methods to combine first and last name in Google Sheets.

We will use some dummy data for our practice as below.

Combine First and Last Name in Google Sheets – Using Ampersand Operator

In this section, we will learn how to combine first and last name in Google Sheets using the ampersand operator, the ampersand is also known as AND operator, it uses a direct formula method to perform so many tasks using different syntaxes, in this specific tutorial we will use ampersand operator as a joiner, we will join tow columns, first the column that has the first name, and then the column that has the second name to make a full name in a single column. This is an easy method, let’s see the below implementation to understand it.

Step 1

Sample data where I have first and last names written in different columns

Step 2

Write the syntax

It is very easy simply you have to write the cell reference + & another cell reference

Step 3

Now if you press Enter key, you will get the combination of first and last name like below

But no, we dont need this, there is no space between first and last names, and it’s not what we wanted

Step 4

To create a delimiter in between names, we need to do a simple change in the syntax, simply add a space between both cell references with the suffix “&”, so we have to use two “&” symbols here like below

Step 5

Now your syntax is completed, press Enter key to get the result

Step 6

You may drag this down to the entire column and get the complete list of first and last name combined in this column.

I hope you find this method easy and very quick.

Note: This method works accurately if you have your first and last name in a non-contiguous column for example if you have your first name in column A and the last name in column K there will be no difference in the formula syntax, you just need to have accurate cell references into the formula.

Combine First and Last Name in Google Sheets – Using CONCAT Function

In this section, we will learn how to combine first and last name in Google Sheets using the CONCAT function. Concat is a built-in function used for concatenation as its name suggests. It’s a handy function with very simple syntax to combine columns. Let’s see how it works practically.

Step 1

Sample data columns

Step 2

Write down the CONCAT function in the cell where you want to get the result

Step 3

Pass the first argument (The first value to be concat)

Step 4

Pass the second argument (The second value to be concat)

Step 5

Your formula is completed, press Enter key to get the result

Here you got two names combined but there is no space in between, and unfortunately, this is a limitation of the CONCAT function, you can not just add a space or any delimiter here using this function. But, we have a more advanced version of the CONCAT function which is CONCATENATE function that allows you to add space or delimiter between two names, so let’s learn it to avoid this space problem.

Combine First and Last Name in Google Sheets – Using CONCATENATE Function

This function helps us to combine two or more names, if someone has a middle name as well, then this function is the best choice. the CONCATENATE function allows you to join more than two names, and allows adding delimiters for every single value, yes even you can add two different delimiters in it for example a space after the first name and a hyphen after the middle name, I know it has no sense but it’s only to let you know the functionality of this function.

Step 1

Sample data, where the first name, middle name, and last name of some people

Step 2

Now write the CONCATENATE function in any cell

Step 3

Pass the first name and then pass a double quote and then add a comma, this is your complete first argument

Step 4

Pass the second name and then again pass a double quote, then a comma.

Step 5

Pass the third name, then no double quotes because we dont need any space after the last name, and there will be no comma because our formula has finished, and we dont want to add further arguments

Step 6

Now we can add a delimiter between double quotes, if you want a space, you can pass single whitespace using the space key one time, or you can pass any character like a hyphen, semi-colon, or comma to use as a delimiter.

Step 7

Press Enter key and here you go, you have successfully combined the first name, middle name, and last name using CONCATENATE function with custom delimiters.

Now there is no limit, you can use it to combine first and last name only, which is the topic of this article, it just allows you more arguments and let you add delimiters as extra functionalities over the CONCAT function

I hope you liked CONCATENATE function and you have learned how it works.

Combine First and Last Name in Google Sheets – Using TEXTJOIN Function

In this section, we will learn how to combine first and last name in Google Sheets using the text join function. This is a little complicated function for newbies, but trust me it’s extremely easy and straightforward. In this section, you will learn to combine first and last name in Google Sheets using text join. Let’s see the syntax first and then we will see the practical implementation.

Syntax

= TEXTJOIN (delimiter, Ignore_empty, text1,text2… )

       Here,

• Delimiter: It is the first mandatory argument, here we define our delimiter, we use double quotes and can pass a space or any character between them to use as our values separator
• Ignore_empty: It’s another mandatory argument that takes the Boolean value True or False, we specify here True to ignore empty values, and false to not ignore empty values
• Text1: It is the first value argument, the cell reference of the first name
• Text2: It is the second value argument, the cell reference for the second name

and so on, you can add more values here to join them.

Let’s now see the practical implementation

Step 1

I have three names here, first, middle, and last, we will combine them using the text join function

Step 2

Write the text join function in any other cell

Step 3

Now pass the first argument (the delimiter inside double quotes)

Step 4

Pass the second argument (True or false to specify sheets to ignore or not ignore empty values)

Step 5

Pass the third argument (here we pass the cell reference for the first name)

Step 6

Pass the fourth argument (here we pass the cell reference for the second name)

Note: Above four are mandatory arguments, now if you want to add more names, you can simply pass the fifth argument and can pass another value

Step 7 (Optional)

Pass the fifth (optional) argument (here we pass the cell reference for the third name)

Step 8

The formula is completed, press Enter key to get the result

This is how you can easily perform the text join function to combine first and last name in Google Sheets like a pro.

I hope you find this tutorial helpful

Download/Copy Google Sheets Workbook

Important Notes

Conclusion

Wrapping up how to combine first and last name in Google sheets, we have learned so many useful methods today, I hope you found this tutorial helpful. For more learning tutorials subscribe to our office demy blog and get regular email updates. Thank you so much.