How to concatenate text cells in a Google Docs Spreadsheet

Let's say you have a bunch of text cells that you want to concatenate into one cell.

For example, you may have labels for different categories stored in column B.
You want to merge a certain set of labels together, and separate them by a blank line.

Simple, use this formula:

=CONCATENATE(B3,char(10),char(13),B5,char(10),char(13),B7,char(10),char(13),B9)

The char(10),char(13) is the ASCII character codes for carriage return, so this is what creates the blank line.

If you want to reference cells on another sheet (e.g. Sheet1) you can do it like so:

=CONCATENATE(Sheet1!B3,char(10),char(13),Sheet1!B5,char(10),char(13),Sheet1!B7,char(10),char(13),Sheet1!B9)

Sorted!
at

6 comments:

  1. Fifth Business11:29 pm

    Thanks, that little post helped me attach Spain to postal codes to use map API in g-spreadsheets to automate driving distance calculation. (I'm not a programmer, but I can concatenate 2 + 2 and get 22...)

    ReplyDelete
  2. Unknown9:19 am

    You're welcome - happy to help!

    ReplyDelete
  3. Anonymous1:46 am

    how can I add to the chain " as a string? It don't let me do this """

    ReplyDelete
  4. Unknown7:58 am

    @Anon - can you explain what you are trying to do?

    ReplyDelete
  5. Anonymous8:57 am

    Think it is important to point out that "char(10),char(13)" produces two carriage returns (Doesn't seem to matter which one of the two ASCII codes in char() are used), thereby not just putting the following on a newline, but actually creating a blank line between previous and following.

    ReplyDelete
  6. Randomtech11:21 am

    @Anon - yes I was going for a blank line in between.

    Good point regarding ASCII 10 vs 13 - either can be used with the same result in this case.

    Thanks for your comment!

    ReplyDelete