Skip to toolbar

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.

Leave a Reply

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

Site Statistics

  • Users online: 0 
  • Visitors today : 4
  • Page views today : 4
  • Total visitors : 49,308
  • Total page view: 67,864

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.