CODEWITHSUNDEEP
google-sheets
Danstan Ongubo
Danstan Ongubo

Reputation: 105

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

Upvotes: 1

Views: 6361

Answers (2)

fullfine
fullfine

Reputation: 1461

As I've seen in the Example Output ==>>, you only had added prefix and suffix once to your block of text. You can add them using CONCATENATE:

Result:

=concatenate("prefix ",ArrayFormula(query(A1:A,,100000)), " suffix")

If I'm misunderstanding your question, tell me exactly what result you expect, based on what data.

Upvotes: 1

player0
player0

Reputation: 1

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)

Upvotes: 1

Related Questions