Make a Boolean Expression From A List of Rows in Google Sheets


We all use search forms every day. There’s usually an advanced search form of some sort that allows you to enter a boolean expression. You know Boolean expressions as “This OR That” – an inclusive search that increases results – or “This AND That” – an exclusive search that limits results.

When you need to filter your results, a powerful method is to make a list of companies, individuals, skills – whatever you are using to get more precise answers – and create a Boolean expression to get a better initial set of results from a search engine. Yet when someone gives you a list of 10, 20, 50, or 100 items the idea of making a delimited list of “Item1” OR “Item2” OR “Item3” is more difficult than you’d like to be.

Never fear: Google Sheets to the rescue! This is a perfect example of a task for a Lazy Programmer™ to solve, as it’s much easier to have a spreadsheet count values and rearrange text for you than it is for you to do the work by hand. Let’s take a look.

As an example, I created a utility spreadsheet that takes a range of values, counts those values, and outputs a result string that delivers a boolean expression to paste into your favorite advanced search page. Whether you’d like to create a list of criteria to find your next job, search Twitter more effectively, or simply create a customized search for Google, this expression builder can help.

This spreadsheet has two formulas that do the work for you. The first one joins a range of values into a concatenated string (turns list of Orange and Apple into ‘Orange’ OR ‘Apple’ OR ”):


There are five functions in this expression:

  1. JOIN combines an expression of values into a string connecting each item with the string ‘ OR ‘;
  2. INDIRECT tells the spreadsheet to wait until the range expression in the formula from A2 to the last unblank value in column A is completed and we know the number of the last row to select;
  3. TEXT converts a complex expression into a value – in this case a calculation of the number of rows in the column of this spreadsheet minus the number of blank rows in the column of this spreadsheet – so that INDIRECT will take result of this expression and substitute “5”, the last row above that has a value. This results in a range from A2:A5 to feed back to JOIN;
  4. ROWS counts the number of rows in a range – the standard number in a Google Spreadsheet is 1000 – and returns an integer to feed back to the expression inside of TEXT;
  5. COUNTBLANK returns the number of blank rows in a range, so that our calculation of rows minus the number of blank rows leaves us with the number of rows containing values (4) or the total number of rows including the header (5).

Now that we have our formula, it creates the string:

'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit' OR ''

From the rows of Orange, Apple, Banana, Grapefruit above. And there is one problem here: that we need to remove the trailing “OR ”” that resulted from the expression.

Our second formula, REGEXREPLACE, removes the last “OR ”” from the string. RegEx is sort of like Black Magic, so you will probably need to use a helper site like Rubular. This string looks for a specific string right before the end of the line (represented by $)

OR ''

and replaces it with nothing.


This leaves you with the strong you want to paste into a search form:

'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit'

You’re all set! Now, use this utility with any number of rows in your list of items to create a Boolean expression made for you automagically.

3 Counter-intuitive ways to use Pinterest

Build a board around an idea

Pinterest is an addictive, visually-oriented way to highlight ideas, arrange and gather images, and understand what memes, colors, and things appeal to your friends and to the marketplace as a whole.

You might be tempted to just “PIN. ALL THE THINGS” and spend lots of time endlessly scrolling the service, and here are three ways you can use Pinterest to find information in new and different ways.

1. search for a concept, not just a person or a thing

Pinterest allows you to search for ideas in addition to people or things. Try searching for something abstract, like freedom and see what you find. You’ll find like-minded individuals (or not-so-like-minded individuals) and people who have expressed your idea in different ways.

2. Find the people who are talking about a meme or a thing

When you’re interested in a certain topic, you may start to see it everywhere. I just started using a Standing Desk, so I used Pinterest to search for all mentions of standing desk to see if there were great setups to learn from and people who were interested in the topic

3. Set up a board to capture ideas on a topic

Pinterest isn’t necessarily set up to be an idea capture, but in a way grabbing pins to link to articles and notes that also happen to have appealing pictures is a perfect method of combining the visual simplicity of Pinterest with the power of Evernote or other note-taking methods.

How are you using Pinterest in an intuitive (or counter-intuitive) way?

Where’s your idea box?

Just Full Of Ideas

Where do you get your ideas? If you’re like me, you get ideas from what you read, who you meet, and from the experiences that make up your day. And sometimes, that idea box is hard to find. Or just plain empty.

Enter Wylio. Introduced to me by my friend Shane Mac, Wylio allows you to search through thousands (millions?) of Creative Commons licensed images until you find the muse for your idea box.

Blogging (or writing in general) shouldn’t be hard, and Wylio helps you break down barriers to posting (I don’t have good content to post with my idea … or I don’t even have an idea). It’s a great idea and I look forward to using it in the future.

So don’t spend your time worrying about what to write.  Use Wylio, give your ideas a vision, and take it from there.  Spend more time writing and less time searching.

Blog at

Up ↑

%d bloggers like this: