Google Sheets How To convert whole column to a string

Question

I have a question. I have a column in Google Sheet something like [here][1]

------------------
| hello           |
-------------------
| my name is John |
-------------------
| Nice to meet you|
-------------------

Question is: How can I get a string like "hello, my name is John, nice to meet you" from it?

What have you tried o far? – Selim Yildiz Dec 15 '19 at 16:20 — Selim Yildiz, Dec 15 '19 at 16:20

player0 · Answer 1 · 2019-12-15T17:28:26.060

5

try:

=JOIN(", ", A1:A3)

or:

=TEXTJOIN(", ", 1, A:A)

if you want it without comma use:

=TRIM(QUERY(A:A,, 99^99))

JOIN and TEXTJOIN are limited to join up to 50 000 characters only. if your dataset is larger do:

=ARRAYFORMULA(QUERY(QUERY(A:A, "where A is not null", 0)&",",,99^99))

edited Dec 15 '19 at 17:28

answered Dec 15 '19 at 16:44

player0

124,011
12
67
124

9^9 is already exceeding the maximum number of cells allowed in a single workbook right? That is a very interesting glitch using headers in query to exceed the maximum allowed by even `&` is there a real character limit per cell I thought it was 50k. Pretty cool. – CodeCamper Dec 15 '19 at 18:46
yes for 9^9 and yes there is a limit but also a workaround how to bypass that limit. for string its this QUERY smash and for formula - https://stackoverflow.com/a/55070275/5632629 – player0 Dec 15 '19 at 19:12
Please provide the link to the bypassing of 5 million cell limit! I can't wait to make almost completely unusable spreadsheets haha! – CodeCamper Dec 15 '19 at 21:36

CodeCamper · Answer 2 · 2019-12-15T15:24:35.973

1

Google sheets has a special function just for this, true here means ignore all the blank cells, and we are assuming you always want a comma and a space after each cell.

=textjoin(", ",true,A:A)

edited Dec 15 '19 at 15:24

answered Dec 15 '19 at 15:22

CodeCamper

6,609
6
44
94

@Datanovice I was assuming the example he gave was just column A:A how do you understand it? – CodeCamper Dec 15 '19 at 15:25
Great answer just tested on a sample sheet. I thought he wanted every word split by a comma! – Umar.H Dec 15 '19 at 15:30

Google Sheets How To convert whole column to a string

2 Answers2