I'm trying to make a formula on Google Sheets that would help me utilize all added keywords from column B into a sourcing boolean string that would include OR and "". (Example "Keyword1" OR "Keyword2" OR "keyword 3" ).

So far, I was able to come up with this, which starts from field B8(based on my doc):

=IF(B8<>"",""""&B8&"""","")&IF(B9<>""," OR "&""""&B9&"""","")&IF(B10<>""," OR "&""""&B10&"""","")&IF(B11<>""," OR "&""""&B11&"""","")&IF(B12<>""," OR "&""""&B12&"""","")&IF(B13<>""," OR "&""""&B13&"""","")&IF(B14<>""," OR "&""""&B14&"""","")&IF(B15<>""," OR "&""""&B15&"""","")

But, the problem that I'm facing, is that if I want to increase the number of keywords beyond 8, I have to manually create every new IF line, one by one, which would take a lot of time (let's say I would have over 500 keywords that I'd like to use and I would need to create 500 BNUMBER lines....).

Is there any way to remodify this formula so that it would include all the keywords that were pasted in the column, no matter the number, or to somehow auto-generate the formula to, let's say, B1000?

  • Hi Dean it would be great if you'd edit your question to explain why you are trying to do this. See The XY Problem It may be that understanding the purpose of the "sourcing boolean string" will help inform the approach that makes the most sense with your actual goal in mind. It is a bit difficult for understand the application as the keywords can be compared without concatenation.
    – Blindspots
    Sep 14 at 22:29

1 Answer 1


Use filter() and join(), like this:

=join(" OR ", filter("""" & B8:B & """", len(B8:B)))

See filter() and join().


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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