It is not possible to specify a range of cells using the CONCATENATE function. Each cell must be specified separately. CONCATENATE (A1:C1) gives an error message and entered as an array formula only the value from A1 is displayed. The correct syntax in this case is:
=CONCATENATE (A1,B1,C1)But if the text from a large number of cells has to be merged, this is a lot of typing with the risk of errors. From Excel 2019, the function TEXTJOIN function is available for this, but for older Excel versions this function is not available. Still, with a small detour, even in older Excel versions, a large range of cells can be merged, without having to type in the cells individually. The example below serves as a starting point.
The intention is to merge the letters from C2:R2 in cell C4. Put the following formula in cell C4: =TRANSPOSE(C2:R2). Then click in the formula bar and then press F9. This makes the contents of the entire cell range appear in the formula bar.
In the formula bar, remove both curly braces and add to the front behind the = -sign CONCATENATE( and put a closing bracket at the back. Close with enter and all letters from C2:T2 appear as one word in cell C4. The formula is then in C4:
=CONCATENATE("m","e","r","g","e","t","e","x","t","e","x","a","m","p","l","e")It is also possible to place a separator between the characters in this way. Put the formula =TRANSPOSE(C2:R2)&”-” and follow the above procedure and the word m-e-r-g-e-t-e-x-t-e-x-a-m-p-l-e appears in C4.
If the data to be merged is not consecutively in a row, but below each other in a column, then the procedure is exactly the same, except that the range without TRANSPOSE must be entered in C4: so =C2:R2 instead of =TRANSPOSE(C2:R2).
The above method gives a formula with a fixed content. If a character is later changed in C2:R2, it will not be reflected in C4. Therefore, it may sometimes be desirable to include the actual cell addresses in the CONCATENATE formula instead of the contents of the cells.. This is especially important if there are multiple rows or columns of data that need to be merged per row or column. Ideally you would therefore have a formula with cell addresses that can be copied to the other rows or columns. This is also easy to achieve with a few small steps. In the example above, the following formula must be placed in an empty row starting in column C:
=ADDRESS(2,COLUMN();4)This formula must then be copied to the right up to and including column R. Then all individual cell addresses of C2:R2 will appear in this row as shown in the screenshot below:
Then put in an empty cell (for example C5) the formula =TRANSPOSE (C4:R4). Click in the formula bar and press F9 and remove both curly braces and replace the = sign with an apostrophe and press enter. Then select the cell where the final merge formula should be and first put the formula: =SUBSTITUTE (C5,"""","").. Then choose copy and then paste special → values. This converts the formula into text. In the formula bar at the front of this text add =CONCATENATE( and a closing bracket at the back. Finally, press enter and the formula is ready:
=CONCATENATE(C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2)Finally, the temporary data in the row of cell addresses (C4:R4) and in cell C5 can be deleted.
Questions / suggestions
Hopefully this article has helped you to merge text in a wide range of cells. If you have any questions about this topic or suggestions for improvement, please post a comment below.
