Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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)).

=JOIN(CHAR(10),B2:B130)

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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