I want to display two cells contents together in another cell. It is easy with one cell, just use = and it is okey. But, can we display two or more cells together in one cell?
You can concatenate the contents of several cells using &
Example: A1=Bad B1=Dog C1=A1&B1 = BadDog
If you want to add a space between two text Values you need to type
C1=A1&" "&B1 = Bad Dog (it’s a space between the double quotes)
This is what I want. Thanks.
You can concatenate the contents of several cells using & Example: A1=Bad B1=Dog C1=A1&B1 = BadDog If you want to add a space between two text Values you need to type C1=A1&" "&B1 = Bad Dog (it’s a space between the double quotes)
In addition to the above answer: there is the CONCATENATE function, which is what I used so far, before seeing Pedro’s answer.
Why use it as it is longer but seems to do the same? Discover-ability (for me) and being more explicit in what you are doing; would consider keeping CONCATENATE for macro’s, for example.
(@Lupp also editing:)
Now and then a user may want to expand this in a way concatenating an arbitrary number of cell contents given by one or more cell ranges e.g.
Starting with V5.2 there was implemented the new function TEXTJOIN which works well for such a task since V5.3, and is capable of inserting a chosen delimiter. There is also a setting to disregard empty content completely. The above example would be solved by
=TEXTJOIN(" "; 0; A1:B1) e.g. returning “Bad Dog”.
Concerning the comment by @Gilberto Schiavinatto I attach this little demo.
Please also note: While testing a bit for this demo I had to experience that TEXTJOIN is still not correctly implemented with some respect. In the most simple cases this may not spoil the usability.
Interesting. But usually the text values don’t include and ending space so concatenating with the function will result in a long word
True, that’s why, just like with your answer, where you add ‘& " " &’ for a space in between the cell references, you’d have to concatenate a space before every next word apart from the first one. As I said: functionally the same, but more verbose. I’ be mostly using ‘your’ & way in future.
Hi @Lupp, I am using version 5.3 (pt_br) and I did not find this function. Please could you please post a file with the function in use. So you could see the translation to pt_br. Grateful
As the function was implemented for compatibility with Excel, the naming should be the same. Excel uses
UNIRTEXTO as the Brazilian name of the function as google and somebody told me.
Ok, thank you.