How to Combine Text from Two Cells in Google Sheets

These methods are helpful most of the time when I work with data that has first and last names, and I need to combine both values in a single cell.

Well, you might have also found yourself in a situation where you must combine values from two cells, right? Let’s take an example…

Let’s say you are organizing an event and have a list of attendees’ first names in one column and last names in another column. You want to create a list of full names for names.

Combine Two Values using the TEXTJOIN Function

TEXTJOIN combines text from multiple cells into one and lets you choose a separator (like a comma or space) between each piece of text.

=TEXTJOIN(" ",True,A2:B2)

The above =TEXTJOIN(” “, True, A2:B2) in Google Sheets combines the text from cells A2 and B2 into one cell, separating them with a space (” “). The TRUE tells the formula to ignore any empty cells if they are there. If one of those cells is empty, it won’t add extra space.

TEXTJOIN is the easiest way to combine values from two or more cells in a single cell. If I had to choose one function over others, I’d choose TEXTJOIN.

Note—You can also choose a range of cells to combine, which saves you time when referring cells in one go.

Using CONCATENATE for Combining Values from Two or More Cells

If you have someone’s first name in cell A1 and their last name in cell B1, you can use the CONCATENATE. The CONCATENATE function merges two or more text items into one single string. You can include values from different cells and add spaces or other characters as a separator by including them between the cell references.

=CONCATENATE(A2, " ", B2)

This formula combines text from cell A2 with the text from cell B2, inserting a space between them using double quotation marks. If you want to combine more than two cells, you need to add the reference to that cell and use a space between again using the double quotation marks.

Using AMEPRSAND to Combine Two Values

And you can also write a simple formula using an ampersand. This formula doesn’t require any function use, but you must use the (&) ampersand to combine values.

=A2 & " " & B2

This formula combines the values from cells A2 and B2 by using the ampersand (&), which acts as a concatenation operator.

Quick Tip: On most keyboards, you can enter an ampersand by holding the Shift key and pressing the 7 key.

Using the CONCATE function to Combine Values in a Single Cell

The CONCAT function merges the text from two cells into one continuous string without separators. This is a limited function when combining values from two or multiple cells.

=CONCAT(A2,B2)

Which Formula is Better to Use

TEXT JOIN is my favorite, but you can choose any of these formulas. Here are a few points to consider:

• =A2 & ” ” & B2 – This is the simplest and most direct method for concatenating text. It’s great for quick combinations.
• =TEXTJOIN(” “, TRUE, A2:B2) – This is the most flexible and powerful option when combining text from multiple cells because it allows you to specify a delimiter and choose whether to ignore empty cells.
• =CONCATENATE(A2, ” “, B2): This function is very clear in its purpose and easy to understand, making it a good choice if you want. But limited also.
• =CONCAT(A2, B2): Similar to CONCATENATE. Unlike CONCATENATE, it doesn’t allow for the direct insertion of separators like spaces, so it should be the last choice.

As I said, TEXTJOIN is my personal favorite. I also feel it’s the best function for combining or merging the values of two or more cells into a single cell.

Combine Values from Corresponding Cells in a Column (Custom Function)

I have also written a custom function code with Google App Script that allows you to combine corresponding cells from two ranges.

function combineCellsRange(range1, range2) {
  // Check if the ranges are arrays and have the same length
  if (Array.isArray(range1) && Array.isArray(range2) && range1.length === range2.length) {
    // Map through each element in the arrays to combine them
    return range1.map((cell, index) => {
      if (range2[index]) { // Check if the second array cell exists
        return cell[0] + " " + range2[index][0]; // Combine and return the value with a space
      } else {
        return cell[0]; // Return the value from the first array if the second is undefined
      }
    });
  } else {
    // Return an error message if the arrays do not match
    return "Error: Ranges must be arrays of the same length.";
  }
}

To use this code, go to Extensions > Apps Script and then paste this code. Now, return to the Google Sheets and enter the formula below to combine values.

=combineCellsRange(A2:A5,B2:B5)