Excel - How to concatenate cell range?

Question

How do I concatenate a range of cells in Excel?

For example: I have a column with many cells containing text information and I would like to concatenate them, but when using the concatenate function error occurs, it is necessary to select one by one of the cells.

The function =CONCATENAR(B2:B48) in this way does not work and it is necessary to rewrite as follows: =CONCATENAR(B2;B3;B4; ... B48).

In search I found suggestion to use a function called unirtexto, but it is not recognized.

Answer 1

If you select cells in a column, it doesn’t know how to concatenate, even.

It has to transpose the "coluninha" to work:

In the target cell, start by typing the function TRANSPOR(),

=TRANSPOR()

Select the crease,

=TRANSPOR(B2:B48)

Add &" " at the end of crease,

=TRANSPOR(B2:B48&" ")

Select the whole function; this part:

TRANSPOR(B2:B48&" ")

Press the F9 key on the keyboard; it will convert the crease into a list. Finish by placing the function CONCAT() around the list,

=CONCAT({"O ", "rato ", "roeu ", ...})

Source: here

Answer 2

If you have Excel 2016, you can use the formula UNIRTEXTO in this way:

=UNIRTEXTO(" ",0,B2:B48)

here to documentation