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.
Sometimes we have to merge values from multiple cells into one cell to achieve some specific task based on the requirements.
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)
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:
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.
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)
Check out more videos and Subscribe my channel.
1024total visits,3visits today