How to Automatically Build Boolean OR Strings

Writing Boolean search strings is typically a quick and simple affair, as most search engines and databases won’t let you construct anything longer than a few hundred characters.

However, if you’re not constrained to a fixed limit on search terms (such as Google’s 32 words) or characters, it’s no longer a simple matter of “this OR that.”

I wrote an article not too long ago in which I illustrated some of the serious limitations associated with using industry filters when searching LinkedIn (or any site, for that matter) for people with specific industry experience. In that post, I demonstrated that when accessing LinkedIn with a free account, there are no search string length limits, allowing you to enter long OR statements with 100’s of companies.

Building large OR strings can be very tedious and time consuming work. Thankfully, John Turnberg graciously commented on my article (thank you John!) and detailed how to use Excel to make quick work of creating large OR strings.

I am not an Excel wizard by any means, so it may have taken me longer than most to take John’s Excel advice and get it to work. If you’re not handy with Excel and would like a turn-key solution, I’ve saved you the effort of trying to build it yourself – you can download a basic Excel Boolean OR builder here:

Boolean OR String Builder

Here’s a quick video clip of how to use it.

Boolean OR String Builder

Concatenation

During my research into how to use Excel to accomplish what I wanted to do, I found out that while it’s easy to add/combine a range of numbers together in Excel (e.g., A1:A100), you can’t concatenate a range of text cells without manually specifying all of the cells in the range (e.g., A1&A2&A3…).

I’m using Excel 2010, and I’m still dumbfounded that you can’t concatenate a range of cells with text values – it seems like something you should be able to accomplish with a click of a button much as you can with AutoSum. Then again, maybe this functionality exists and I just haven’t found it yet?

Knowing that I could not possibly be the only person annoyed by this lack of simple functionality, some quick Internet searching led me to a few sites that claimed you actually could concatenate a range of cells, and one site showed how to do it with a VBA function.

Now, I’ve never used VBA, but I am a bit of a hacker (as much as a non-technical person can be) and I managed to get it working. You can download it here:

How to use Excel and VBA to quickly and easily create Boolean OR strings

Once you download the file, you should notice this warning:

Be sure to “Enable Content,” otherwise the macros won’t work.

Here’s a quick video clip of how to use it:

How to use Excel and VBA to quickly and easily create Boolean OR strings

Using Company Lists to Build OR Strings

There are many sites you can use to find lists of target companies by size and by industry. If you’re searching for Fortune 500 companies, you could of course go straight to the source. However, when copying and pasting from various websites, you may pick up some unwanted and unseen garbage formatting, even if you copy to Notepad first before entering the list into Excel.

When copying and pasting lists from Notepad to Excel, it is safest to be sure to right-click, select “Paste Special” and then select Text. I’ve had a few instances of trying to build OR strings from Notepad and an extra space was hiding at the end of every company name after pasting into Excel (e.g., “Lockheed Martin “), which ruined the integrity of the string.

Paste_Special

Using LinkedIn to Build Company/Industry Boolean OR Strings

LinkedIn is a fantastic source of industry and company information – here’s a short video clip of how you can build industry-specific company lists using LinkedIn and one of the above Excel OR builders:

Video_Playback_Image_Generic

Cleansing and Improving your Company Strings

Ensure your data is clean by using Notepad to strip unwanted formatting before copying into Excel to build your OR strings, paying attention to lookout for hidden spaces.

Additionally, look over your list to identify opportunities to improve your string by adding variations of the company names that people are likely to use (abbreviations, adding/removing spaces between two or more names, etc.).

For example, although the company is listed as “J.P. Morgan Chase & Co.” on the Fortune 500 list, people are more likely to enter it as JPMC, or JPMorganChase, JP Morgan Chase, etc. Essentially, be sure to examine all of the companies in your list with a keen eye for any and all possible variations that people may use, even if they are quite wrong in doing so, as you cannot find what you don’t search for.

This is true in less obvious cases, such as Lowes vs. Lowe’s and Walgreen vs. Walgreens. To test whether or not people use a possible variant, you can isolate a test variant in LinkedIn like so, using the company search field: Lowes NOT Lowe’s, Walgreen NOT Walgreens. The force is strong with the NOT operator.

Also be on the lookout for common denominators of many/all potential variations. For example, a good number of companies have more than 1 word in their name, such as Honeywell International, Lockheed Martin, Publix Super Markets, Kraft Foods, Medco Health Solutions, Verizon Communications, etc. In those cases, there is only a very small risk of yielding false positive results if you simply searched for Honeywell, Lockheed, Publix, Kraft, Medco, and Verizon.

Here is a cleansed and modified Fortune 100 list I put together with 129 terms:

Example of Fortune 100 Boolean OR search string using the automated Boolean OR statement builder

Once you download the file, you should notice this warning:

Be sure to “Enable Content,” otherwise the macros won’t work.

When you enter that string into LinkedIn’s company field and execute the search, you may notice a few results that seem to defy the logic of the string. If you’re up for a challenge – can you tell me why these exceptions happen? :-)

Using Word to Automatically Build Boolean OR Strings

As long as you are not building strings with multi-word phrases requiring the use of quotation marks, you don’t have to use Excel to build long OR statements – you can actually use Word to quickly build Boolean OR strings.

For example, if you are interested in using LinkedIn for diversity sourcing, you can use Word to build first name-based OR strings, which would consist of a string of single word search terms which don’t require the use of quotation marks. In fact, one of the only reasons for using Excel instead of Word to build Boolean OR strings is because most search engines (including LinkedIn) don’t play nice with Word’s quotation marks. More on that in a moment.

Watch this quick video demonstrating how easy it is to use Word to create a Boolean OR string:

Video_Playback_Image_Generic

Wow – that was easy!

You could of course find ^p and replace all with ” OR “, but you will discover that LinkedIn and other search interfaces/engines won’t like the quotes generated by Word.

Feedback and Suggestions

Let me know if you find the Excel Boolean OR builders and the video overviews helpful, and don’t hesitate to let me know if I missed anything, if they weren’t clear and easy to understand and use, or if you have suggestions for improvements – I’d be happy to make them better!