I'm trying to write a simple formula that concatenates each entry in a given column of a spreadsheet, interspersed with newline characters. In other words,

=CONCATENATE(B2, CHAR(10), B3, CHAR(10), B4, CHAR(10), ... , CHAR(10), B130)

Is there any way of achieve this without having to type the whole thing out by hand? Even copying and pasting so I just have to modify the cell references is pretty laborious. I was thinking that there might be some way of implementing a for loop?

1 Answer 1


You need the JOIN function. This function joins all the elements in an array (ie. a 1-dimensional range, a single column or a single row) separated by a common delimiter (your CHAR(10)).


I believe the CONCATENATE function has a limit of 30 arguments, so that could also have been a problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.