0

My exact formula below used to work previously and without changing anything except adding more data to my master table whereby 'Premium Advertisers' is populated, I get the following error:

"Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "s "" at line 1, column 2168."

Please see my query below, doesn't make sense why it's picking up an error in the 2168th character of my select statement. I've tried only using a small sample of my master data table and I still get the same error.

=QUERY('Premium Advertisers'!A2:G, "select * where A matches '" & "^" & textjoin("$|^", true, 'Premium Advertisers'!I2:I) & "$' ",0)
player0
  • 124,011
  • 12
  • 67
  • 124
TheReibahs
  • 23
  • 2
  • it's hard (or even impossible) to tell where the problem is without seeing the sheet. Could you share a link to it? – z'' Mar 30 '22 at 00:33
  • @ztiaa https://docs.google.com/spreadsheets/d/1sSsYMaMPmOem6UWfdDa9PdDiW0S5vJeAueqM42kmY0g/edit?usp=sharing – TheReibahs Mar 30 '22 at 06:04
  • That example actually works but the one I'm talking about is pretty much the same except with actual advertiser names and a lot longer but gives me the above error – TheReibahs Mar 30 '22 at 06:05

1 Answers1

1

you got there a name which contains ' (Hill's Pet Nutrition South Africa (Pty) Limited). therefore try:

=INDEX(SUBSTITUTE(QUERY(SUBSTITUTE('Premium Advertisers'!A2:G, "'", "♦"), 
 "where Col1 matches '^"&TEXTJOIN("$|^", 1, 
 SUBSTITUTE('Premium Advertisers'!I2:I, "'", "♦"))&"$'", ), 
 "♦", "'"))

enter image description here

update

we also need to skip reserved regex characters like parenthesis () with \(\)

=FILTER(A:D, REGEXMATCH(A:A, TEXTJOIN("|", 1, 
 SUBSTITUTE(SUBSTITUTE(I2:I, "(", "\("), ")", "\)"))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • this doesn't work, I get a formula parse error. My previous formula actually works when I limit my matchup column (column i) to 84 entries, the moment I exceed 84 I get my original error message. Any idea why this may be the case? – TheReibahs Mar 31 '22 at 10:17
  • @TheReibahs can you share a copy of your sheet? – player0 Mar 31 '22 at 10:34
  • https://docs.google.com/spreadsheets/d/1ypq3OzETDMbLaXuGdSg_9ioohpDuZVrrS2jMhkSgoTU/edit?usp=sharing – TheReibahs Mar 31 '22 at 10:42
  • @TheReibahs answer updated – player0 Mar 31 '22 at 10:59
  • so doing it this way doesn't crash the formula, however it doesn't seem to be returning all the data. There's 100 premium advertisers and it's only returning 79 unique ones and their data. So to recap, this way works in that it doesn't crash but is not returning all the data. Any idea why that may be? – TheReibahs Mar 31 '22 at 12:54
  • @TheReibahs answer updated – player0 Mar 31 '22 at 13:46
  • 1
    thanks a mill mate, this seems to work smooth! – TheReibahs Mar 31 '22 at 14:26