CODEWITHSUNDEEP
if-statementgoogle-sheetsconcatenationtextjoinifs
Ranjan Kumar Singh
Ranjan Kumar Singh

Reputation: 23

How to exact match withing column using QUERY in Google Sheets?

I have some values in the cell AA1 as

'SONA'|'SONAKSHI'|'SUMON'|'SUMONA'

But it gives result as

SONA'|'SONAKSHI'|'SUMON'|'SUMONA'

I have created a formula,

=let(cell,indirect($CA$27&"1"), ifs(cell<>"", " AND "&CC27&" MATCHES '.*("&UPPER(cell)&").*' " , cell="",""))

It gives result as

AND Col7 MATCHES '.*(SONA'|'SONAKSHI'|'SUMON'|'SUMONA').*'

' is missing in the starting. How to fix it. Please help me.

My expected result is

AND Col7 MATCHES '.*('SONA'|'SONAKSHI'|'SUMON'|'SUMONA').*'

Upvotes: 0

Views: 70

Answers (3)

rockinfreakshow
rockinfreakshow

Reputation: 30309

Your original formula needs no change & will work as intended if you escape the leading(first) single quote ' in Cell_AA1 with an additional quote as such ''SONA'|'SONAKSHI'|'SUMON'|'SUMONA'

  • leading(first) single quote is kinda intended to treat the cell content as text & that's the reason indirect function is not considering it & you are ending up with SONA'|'SONAKSHI'|'SUMON'|'SUMONA'

Upvotes: 1

player0
player0

Reputation: 1

My expected result is

AND Col7 MATCHES '.*('SONA'|'SONAKSHI'|'SUMON'|'SUMONA').*'

"matches '.*('"& UPPER(cell) &").*'"

enter image description here

enter image description here

try:

=IF(AG1 = "", LET(cell, INDIRECT($CA$27&"1"), 
 IFS(cell<>"", " AND "&CC27&" MATCHES '.*('"& UPPER(cell) &").*' " , cell="","")), )

enter image description here

Upvotes: 1

Karthikeyan K
Karthikeyan K

Reputation: 11

It seems like the single quotes are causing issues in your formula. To fix this, you can escape the single quotes by adding an extra single quote before each single quote within the cell values. Here's how you can modify your formula to achieve the expected result:

=LET(cell,INDIRECT($CA$27&"1"), IF(cell<>"", " AND "&CC27&" MATCHES '.*("&UPPER(SUBSTITUTE(cell,"'","''"))&").*' ", cell="",""))

By using the SUBSTITUTE function to replace single quotes with double single quotes (''), you ensure that the single quotes within the cell values are properly escaped. This should give you the expected result:

AND Col7 MATCHES '.('SONA'|'SONAKSHI'|'SUMON'|'SUMONA').'

Upvotes: 1

Related Questions