Concatenating with line breaks, with blank columns ignored (data in ascending order 1 to 10)

[Aadam]

I have a formula which concatenates queries (from construction company to employer) so that it appears with line breaks in a workflow pdf report. I used to have the queries just in one query column, but I am now using a Hyperlink to google form response for the client to be able to respond from the workflow email. The problem is I can’t figure out how to keep line breaks as the url is referencing to the cell value (so in the google sheet cell there could be Query1 line break Query 2 line break Query 3 etc). It’s working now the way I want it to, but how do I adapt formula to ignore lets say query5 to query10 if there is no data filled in there? This is so that I don’t get unecessary spaces below queries in my reports. Note. Currently the queries build from 1, so if there is 3 queries then there will be Query1, Query2, Query3 blank columns for the remaining Query4 to Query10.

Appsheet Formula currently using below;

CONCATENATE( [QUERY1],"
“,[QUERY2],”
“,[QUERY3],”
“,[QUERY4],”
“,[QUERY5],”
“,[QUERY6],”
“,[QUERY7],”
“,[QUERY8],”
“,[QUERY9],”
",[QUERY10])

Apolgies I found a few topics covering line breaks, but could not find how to ignore blank “columns”

[jyothis_m]

Aadam:

CONCATENATE( [QUERY1],"> “,[QUERY2],”> “,[QUERY3],”> “,[QUERY4],”> “,[QUERY5],”> “,[QUERY6],”> “,[QUERY7],”> “,[QUERY8],”> “,[QUERY9],”> ",[QUERY10])

@Aadam Can you please try this and check whether it helps

CONCATENATE(IF(ISNOTBLANK([QUERY1],([QUERY1],"
“),“”)),IF(ISNOTBLANK([QUERY2],([QUERY2],"
“),“”)),IF(ISNOTBLANK([QUERY3],([QUERY3],"
“),“”)),IF(ISNOTBLANK([QUERY4],([QUERY4],"
“),“”)),IF(ISNOTBLANK([QUERY5],([QUERY5],"
“),“”)),IF(ISNOTBLANK([QUERY6],([QUERY6],"
“),“”)),IF(ISNOTBLANK([QUERY7],([QUERY7],"
“),“”)),IF(ISNOTBLANK([QUERY8],([QUERY8],"
“),“”)),IF(ISNOTBLANK([QUERY9],([QUERY9],"
“),“”)),IF(ISNOTBLANK([QUERY10],([QUERY10],"
“),“”)))

[Aadam]

jyothis.m:

CONCATENATE(IF(ISNOTBLANK([QUERY1],([QUERY1],“> “),”“)),IF(ISNOTBLANK([QUERY2],([QUERY2],”> “),“”)),IF(ISNOTBLANK([QUERY3],([QUERY3],“> “),”“)),IF(ISNOTBLANK([QUERY4],([QUERY4],”> “),“”)),IF(ISNOTBLANK([QUERY5],([QUERY5],“> “),”“)),IF(ISNOTBLANK([QUERY6],([QUERY6],”> “),“”)),IF(ISNOTBLANK([QUERY7],([QUERY7],“> “),”“)),IF(ISNOTBLANK([QUERY8],([QUERY8],”> “),“”)),IF(ISNOTBLANK([QUERY9],([QUERY9],“> “),”“)),IF(ISNOTBLANK([QUERY10],([QUERY10],”> “),“”)))

Hi Jyothis, thanks for the response.

After a few attempts I noticed that the workflow pdf report and the workflow email template (where formula is in table) was doing it own cropping and not extending past empty columns, so I have left the formula as is without the IF(ISNOTBLANK) so that the formula stay a bit smaller.

Screenshot below of report;

I picked up another issue in the google form it was taking the number 10 as being 1 in the url link. So I reduced to 9 queries only.
https://docs.google.com/formsblahblah918=A1 A2 A3 A4 A5 A6 A7 A8 A9 (%0A%0AA10 This last part was giving a 1 value. in google form and giving me the same query as text value in Query 1)

I think this is a question for another forum.

[Steve]

Try this:

SUBSTITUTE(
  CONCATENATE(
    LIST(
      [QUERY1],
      [QUERY2],
      [QUERY3],
      [QUERY4],
      [QUERY5],
      [QUERY6],
      [QUERY7],
      [QUERY8],
      [QUERY9],
      [QUERY10]
    )
    - LIST("")
  ),
  " , ",
"
"
)

[jkevz]

@Steve I have tried this but it shows an error. Please find screenshot below. Please advise what did I missed to check.

[Steve]

Try something like this instead:

SUBSTITUTE(
  CONCATENATE(
    LIST(
      TEXT([QUERY1]),
      TEXT([QUERY2]),
      ...,
      TEXT([QUERYn])
    )
    - LIST("")
  ),
  " , ",
"
"
)

In other words, wrap each item with TEXT().

[jkevz]

@Steve Thank you! Solved!