CODEWITHSUNDEEP
google-sheetsconcatenationgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Antimoany
Antimoany

Reputation: 75

Concatenate column with identical characters between rows

Another difficult-to-search problem:

I want all the data in a certain column to appear in a single cell elsewhere. CONCATENATE does that, but not the way I need. So far I have 

=CONCATENATE(Sheet1!A3:A999)

I've seen other spreadsheets in which the cell inserts an @ before each value and space afterward. That's exactly what I want to do here. I'd steal the formula directly from one of those sheets but I can't seem to find any at present, and can't figure out how to search for exactly this without getting 500 results that just mention the words "google sheets" somewhere.

Upvotes: 1

Views: 69

Answers (2)

Александр Ермолин
Александр Ермолин

Reputation: 1165

It is a pity that you did not attach your source data and the desired result explicitly. I mean sample range, not 1000 cells. But as far as I understand, you would like to get this formula, based on ARRAYFORMULA feature:

=CONCATENATE(ARRAYFORMULA(CONCAT(CONCAT("@";Sheet1!A3:A999); " ")))

Am I right?

Note! If you want to exclude empty cells from the range, you can use FILTER as follows:

=CONCATENATE(ARRAYFORMULA(CONCAT(CONCAT("@";FILTER(Sheet1!A3:A999; NOT(ISBLANK(Sheet1!A3:A999)))); " ")))

Upvotes: 2

player0
player0

Reputation: 1

all you need is this:

=ARRAYFORMULA(TRIM(QUERY(IF(LEN(Sheet1!A3:A), "@"&Sheet1!A3:A, ),,999^99)))

enter image description here

Upvotes: 1

Related Questions