Google Sheet || Use Concatenate or TextJoin to merge multiple cell values.

This blog contains multiple ways to merge or concatenate multiple cell values in one cell. To achieve this we could use Concatenate or TextJoin methods.

Google sheet tips.

Sometimes we have to merge values from multiple cells into one cell to achieve some specific task based on the requirements.

CONCATENATE

Basic formula is =CONCATENATE(string1, [string2, …])

  • We could pass parameters in below format:
    • Individual cell reference like =Concatenate(A1,A2,A3…)
    • Multiple values like =Concatenate(“This”, “is”, “a”, “Computer.”)
    • Cell range reference like =Concatenate(A1:A10)
Example of Concatenate method without space.

In the above example, we have to merge cell values from A13 to A16. Here we can observe that this method is not inserting any space between the words by default. It just appending the values given in a range of references.

Concatenate method with spaces

To insert spaces between words we have put these spaces manually as mentioned in the below example:

Concatenate method with spaces.

Note: When values are less we could use this method, but suppose when we need to implement this method for multiple values it might be challenging to maintain it.

TEXTJOIN

The TextJoin method has overcome the above limitation. The basic formula is:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

  • Parameters in the TEXTJOIN method:
    • delimiter: Character like space, hyphen or comma to separate the words.
    • ignore_empty: Boolean value TRUE/FALSE, to ignore the blank cells while joining values.
    • text1, text2: Cell reference, cell range or string values.

In this method, we don’t have to worry about the spaces between words. See below example, we have to again merge the cells from A13 to A16. Here we don’t need to mention spaces after each word. However, we can declare space one time as a delimiter.

Use of “ignore_empty” attribute

Suppose we have two blank cells present between the range A13 to A18 as mentioned in the below image.

  • Then if we set the value of “ignore_empty” as:
    • TRUE: blank cells would ignore while merging data
    • FALSE: blank cells would also be included in the final string value

We could observe that the String generates via formula having FALSE value has extra space between the word “is” and “a” (highlighted in yellow color).

Step by Step guide(Video)

Step by Step Guide to use Concatenate or TextJoin method.

Check out more videos and Subscribe my channel.

385total visits,9visits today

Leave a Reply

Your email address will not be published. Required fields are marked *