How to Combine First and Last Name in Google Sheets

How to Combine First and Last name in Google Sheets 27

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.

See also  Calculate Average, Mean, Mode and Standard Deviation in Google Sheets

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

How to Combine First and Last name in Google Sheets 1

Step 2

Write the syntax

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

How to Combine First and Last name in Google Sheets 2

Step 3

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

How to Combine First and Last name in Google Sheets 3

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

How to Combine First and Last name in Google Sheets 4

Step 5

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

How to Combine First and Last name in Google Sheets 5

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.

How to Combine First and Last name in Google Sheets 6

How to Combine First and Last name in Google Sheets 7

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.

Tip: You can use any delimiter like space, dash, hyphen comma, semi-colon, underscore, etc. Simply add that delimiter between the double quotes between the two & signs.

See also  How to Insert Degree Symbol in Google Sheets [5 Methods]

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

How to Combine First and Last name in Google Sheets 8

Step 2

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

How to Combine First and Last name in Google Sheets 9

Step 3

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

How to Combine First and Last name in Google Sheets 10

Step 4

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

How to Combine First and Last name in Google Sheets 11

Step 5

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

How to Combine First and Last name in Google Sheets 12

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

How to Combine First and Last name in Google Sheets 13

Step 2

Now write the CONCATENATE function in any cell

How to Combine First and Last name in Google Sheets 14

Step 3

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

How to Combine First and Last name in Google Sheets 15

Step 4

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

How to Combine First and Last name in Google Sheets 16

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

How to Combine First and Last name in Google Sheets 17

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.

How to Combine First and Last name in Google Sheets 18

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.

How to Combine First and Last name in Google Sheets 19

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.

See also  How to Put Fractions in Google Sheets [Guide 2022]

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

How to Combine First and Last name in Google Sheets 20

Step 2

Write the text join function in any other cell

How to Combine First and Last name in Google Sheets 21

Step 3

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

How to Combine First and Last name in Google Sheets 22

Step 4

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

How to Combine First and Last name in Google Sheets 23

Step 5

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

How to Combine First and Last name in Google Sheets 24

Step 6

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

How to Combine First and Last name in Google Sheets 25

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)

How to Combine First and Last name in Google Sheets 26

Step 8

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

How to Combine First and Last name in Google Sheets 27

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

  • There are more methods like the auto-fill pattern method, apps script method, and so on
  • All the methods are quite handy, but I would recommend using CONCATENATE and TEXTJOIN because they give you more control over your data

See also  How to Compare Two Columns In Google Sheets (4 Methods)

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general