Search code examples
google-sheets

How to add prefix and suffix text to to part of text in an arrayformula

I am trying to concatenate some block of text with prefix and suffix text.

I already have a working formula which returns the required block of text. The challenge is concatenating it with the prefix and suffix text. I am using this =ArrayFormula(query(A1:A,,100000)) which allows me to have more than 50,000 characters in a cell. So the prefix needs to come just before A1:A and the suffix after.

So that the formula looks something like =ArrayFormula(query("Prefix"&A1:A&"Suffix",,100000)). I tried this but it ruins the formula and doesn't return all the results. I'm not sure why.

Here is a link to my spreadsheet - https://docs.google.com/spreadsheets/d/1SVQtzoeo3A5g2vjNK1k4yltwEIVQssanw3ulH3a3iH0/edit?usp=sharing

Solution

  • use:

    =QUERY(INDEX(SORT(FILTER({"Prefix "&Sheet6!B:B&" Suffix", 
 ROW(Sheet6!A:A)}, Sheet6!A:A=A5), 2, 0),,1),,100000)

    enter image description here

    update 1:

    ="Prefix "&QUERY(INDEX(SORT(FILTER({Sheet6!B:B, 
 ROW(Sheet6!A:A)}, Sheet6!A:A=A5), 2, 0),,1),,100000)&" Suffix"

    enter image description here

    update 2:

    =QUERY({"Prefix"; INDEX(SORT(FILTER({Sheet6!B:B, 
 ROW(Sheet6!A:A)}, Sheet6!A:A=A5), 2, 0),,1);"Suffix"},,100000)