I want to concatenate the data from each column of a row in an Excel into a single cell. So for example, if I had the following:
first_name last_name country
Demetra Ioannou US
Emmalyn Nicolls US
Daron Dimitriades DE
I'd want to change it to this
first_name last_name country concatenated
Demetra Ioannou US DemetraIoannouUS
Emmalyn Nicolls US EmmalynNicollsUS
Daron Dimitriades DE DaronDimitriadesDE
The spreadsheet has a large number of rows, so instead of specifying each cell individually, (A2 & B2 & C2 & etc), I'd like to use a formula to concatenate the entire range. If I use CONCATENATE(A2:C2), the values get spread across multiple cells.
first_name last_name country concatenated
Demetra Ioannou US Demetra Ioannou US
Emmalyn Nicolls US Emmalyn Nicolls US
Daron Dimitriades DE Daron Dimitriades DE
A guide from Excel Champs suggests using TRANSPOSE on the range then hitting F2 to replace the formula with values, but I would that would require manually repeating the process for every row in the spreadsheet.
How can I combine the values from a range of cells into one cell without having to specify every cell individually?
I don't think the proposed duplicates are good replacements for this question.
The first proposed duplicate asks how to combine cells with a separator between them, which is a related but distinct problem. The existing answer uses CONCAT by manually specifying each cell in the range, which my question explicitly asks to avoid. (Relatedly, I think their question is better solved with TEXTJOIN than CONCAT, so I left my own answer). The proposed duplicate target is also a bit unclear, asking for multiple things. (Combine data from multiple cells, add a separator, append .pdf, make names lowercase for filename)
The second proposed duplicate specifically asks about how to use TEXTJOIN, where CONCAT would be a better solution in the proposed question here.
Use CONCAT instead of CONCATENATE to combine the results into a single cell.
=CONCAT(A2:C2)
According to Microsoft's documentation, CONCATENATE was replaced by CONCAT in Excel 2016 and later. The newer function CONCAT strips out the delimiters between the cells being combined. If you do want to include delimiters, Microsoft recommends using TEXTJOIN instead.