Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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

share|improve this answer

Your Answer


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.