How to Automatically Build Boolean OR Strings

Posted by | September 07, 2010 | Boolean, How-To's | 17 Comments

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!

Related Posts Plugin for WordPress, Blogger...

About Glen Cathey

Glen Cathey is a sourcing and recruiting thought leader with over 16 years of experience working in large staffing agency and global RPO environments (>1,000 recruiters and nearly 100,000 hires annually). Starting out his career as a top producing recruiter, he quickly advanced into senior management roles and now currently serves as the SVP of Strategic Talent Acquisition and Innovation for Kforce, working out of their renowned National Recruiting Center with over 300 recruiters. Often requested to speak on sourcing and recruiting best practices, trends and strategies, Glen has traveled internationally to present at many talent acquisition conferences (5X LinkedIn Talent Connect - U.S. '10, '11, '12, Toronto '12, London '12, 2X Australasian Talent Conference - Sydney & Melbourne '11, '12, 6X SourceCon, 2X TruLondon, 2X HCI) and is regularly requested to present to companies (e.g., PwC, Deloitte, Intel, Booz Allen Hamilton, Citigroup, etc.). This blog is his personal passion and does not represent the views or opinions of anyone other than himself.

  • http://wisemansay.co.uk/blog/ Hung Lee

    Must read for any recruiter worth his salt. Glen, this is a fantastic post and tremendous value – can’t believe you’ve only had two retweets on it so far. Consider it now 3.

  • http://www.hrbuoy.com Chris Frede

    Great post, thank you for the detail. Especially love the excel piece and company strings.

    Oh, and the video is awesome! I will be singing it all day.

  • http://www.linkedin.com/in/yogeshsourcinghunk Yogesh Kumar

    Hi Glen,
    This is my one of the favorite post from you. I have been utilizing Excel and VBA to automate your Strings.
    All the techniques you shared above are great and beneficial for Sourcer/ Recruiter.

    It can be utilized to next level and great things can be done.

    Here I would like to contribute to your website with one of article as how to leverage Excel as your SEARCH TOOL…I repat, Yes Search Tool.

    I have been doing it for the last almost two years and would love to share best practices.

    Thanks again for contributing to enhance Sourcing Folks knowledge.

    Thanks,
    YK

  • Sourcing Samuri

    Too bad LinkedIn now has a 500 character limit in the “Company” field under advanced search (at least in LinkedIn Recruiter….). It is a sad day for Sourcers :-(

  • Adam Wiedmer

    I love the Concatenate feature in Excel and have been using it for a long time. There’s actually a way to skip the step you have in ColumnB and use the Concatenate Text2 field in Excel to do this. Concatenating the original formula will auto-include the ” OR “.

    This is also big for developing targeted email campaigns from names at a company off LinkedIn etc. You can use Concatenate AND “Text To Fields” Excel function to separate & combine FirstName + “.” + LastName +”@” + “COMPANYDOMAIN.com” etc, I’d recommend experimenting with it a bit.

  • Brooke F

    Glen,
    Thank You for the resources. I find these tips and tricks to be, an intricate part of my recruiting evolution. Thank You!
    Looking forward to the next article.

  • Justin Giovinco

    After a 3 month battle w/ the LinkedIn dev team, we have finally convinced them to remove the “character limits” in the “company” field on the search page. Us Sourcers can now go back entering “very long” search strings in those fields on the search page.

    Happy Hunting!

  • http://twitter.com/PhilipsJobsNA Amie Cafferty

    Thanks for your insights.  This is great stuff for my team.  Do you know if with the new format fo LI the companies when I search them come up in more detail than the list you showed are there settings we can change for this?  thanks for your help!

  • http://www.booleanblackbelt.com Glen Cathey

    Amie,
    You’re welcome!

    However, I’m not exactly sure what you’re specifically asking for. What are you referring to with regard to the new format to the LinkedIn companies?

  • Pingback: How to Convert Quotation Marks in Word for Boolean Searches

  • Pingback: LinkedIn’s Talent Connect, Talent Pipeline, and Certification

  • Joannaransley

    Good morning Glen, in regard to what Amie was saying: how do you cut through the clutter and company logos to create lists nowadays?  Notepad and lots of deleting?? Thank you for everything that you share by the way. I want to be Glen Cathey if I grow up!!

  • evan bran

    This is a fantastic article. The codec for your videos is expired (outdated?). Would you please repost videos on youtube? I really am quite interested in seeing them.

  • http://www.booleanblackbelt.com Glen Cathey

    Thanks Evan! I just double checked the videos and they download and play for my in Windows Media Player. What is odd is that I originally posted these so they would play in your browser – not sure why you have to download and then play them. Good Youtube call – I will get on that suggestion shortly.

    Thanks!

  • Jim Stephenson

    Does anyone know if there is a character or word limit for OR strings in the company, title, keyword fields in LInkedin Recruiter search?

  • Christine Z

    Hi Glen: Do you offer any training in how to write Boolean strings? or do you know where I can learn the basics? I dont know anything about boolean strings and I need to learn for my recruiting business. Thanks, Christine

  • Pingback: 100+ Free Sourcing & Recruiting Tools, Guides, and Resources