2

I'm using a formula to run through a list of names, compare them to a master list and check associated values for the names that appear on both.

=COUNTA( Iferror(QUERY(Requirements!A2:F, "select A where A matches '" & JOIN("|", Attendance!A2:A) & "' and B = 'Yes'", 0),))

However, some of the names contain apostrophes (eg. O'Malley) and this is killing the formula - Is there a simple way to escape or replace the apostrophe in the name either in the original lists or as the formula reads it?

3

According to Google's Query Language Reference, string literals should be

enclosed in either single or double quotes. Examples: "fourteen" 'hello world' "It's raining".

You are joining the Attendance values by | only, which again is enclosed in 's, so if Attendance!A2:A was

Larsson
O'Malley
Taylor

the result of the "... '" & JOIN("|", Attendance!A2:A) & "' ..." would be 'Larsson|O'Malley|Taylor'.

As you can see, this leads to unmatched ' - the string seemingly ends and begins with a ', but the apostrophe in O'Malley shortcuts the string so that it is really 'Larsson|O'.

You could use "" instead of ' when creating the string:

=COUNTA( Iferror(QUERY(Requirements!A2:F, "select A where A matches """ & JOIN("|", Attendance!A2:A) & """ and B = 'Yes'", 0)))

Note how the double quotation mark "" allows the character to be escaped, so it does not short-circuit the string.

This would allow O'Malley to be counted, but would be trouble if you have a name that includes ". To avoid all sorts of character issues, your formula should be rewritten so that it does not rely on creating a query by concatenating input strings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.