Concatenate query output fails to format

Skip to first unread message

Peter Dow

Aug 9, 2021, 2:10:05 AMAug 9
to Google Visualization API
Given SheetOne that looks like this:

     A                    B                     C
1   05/01/1950 This is the first comment line   =char(10)
2   10/15/2005 This is the 2nd comment line     =char(10)

And on SheetTwo

1  =concatenate( query( SheetOne!$A1:$C5, "select A, B, C format A 'yyyy-mm-dd'",0) )

What displays on SheetTwo in cell A1 is

18384This is the 1st comment line.
38671This is the 2nd comment line.

Apparently the query formatting is applied after the results are entered in cells, and not before the results are passed to the concatenate() function.

What I would like is formatting applied before the results are returned.  A scalar formatting function would be nice, and so would adding the char() scalar function, so the results returned to concatenate() are already formatted. Also, allowing a literal to appear more than once in the select field list would make it more like SQL and much more flexible.

Then I could make the formula for SheetTwo A1 something like:
1  =concatenate( query( SheetOne!$A1:$C5, "select format(A,'yyyy-mm-dd'), ' ', B, char(10)'",0) )

And have the display on SheetTwo A1 look like this

1950-05-01 This is the 1st comment line.
2005-10-15 This is the 2nd comment line.

Reply all
Reply to author
0 new messages