How to Combine Text from Two Cells in Google Sheets

puneet-gogia-excel-champs

- Written by Puneet

Most of the times when I work with data where I have first and the last names, and I need to combine both values in a single cell, that’s where these methods come helpful.

combine-text-from-two-cells

Well, you might have also found yourself in a situation where you need to combine values from tow 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 name.

Combine Two Values using 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.

combine-two-values-using-textjoin
=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 and quickest way to combine values from two or multiple cells in a single cell. If I have to choose one function over others, I’d choose TEXTJOIN.

Note – You can also choose a range of cells to combine which saves your time in 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 for that. CONCATENATE function is used to merge two or more text items into one single string. You can include values from different cells, and even add spaces or other characters between as a separator by including them between the cell reference.

concatenate-to-combine-values
=CONCATENATE(A2, " ", B2)

This formula combines text from cell A2 with the text from cell B2, inserting a space between them by using double quotation marks. And 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 need to use the (&) ampersand to combine values.

combine-two-values-using-ampersand
=A2 & " " & B2

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

Quick Tip – You can enter an ampersand on most of the keyboards by holding the Shift key and pressing the 7 key.

Using CONCATE function to Combine Values in a Single Cell

The CONCAT function merges the text from two cells into one continuous string without any separators. This is limited function to use when you want to combine values from two or multiple cells.

concate-to-combine-values
=CONCAT(A2,B2)

Which Formula is Better to Use

TEXT JOIN is my personal favourite to use, but you choose any of these formula as per your need. Here are a few points to consider:

  • =A2 & ” ” & B2 – This is 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 you need to combine 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 you to directly insert separators like spaces. And that’s why it’s should the last choice to use.

As I said, TEXTJOIN is my personal favourite, and I also feel it’s the best function to combine or merge values two or more cells into a single cell.

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

And I have written a custom function code with Google App Script which 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 the Extensions > Apps Script and then paste this code there. Now comeback to the Google Sheets and enter the below formula to combine values.

code-to-combine-values
=combineCellsRange(A2:A5,B2:B5)
Last Updated: May 05, 2024