If you need to create a filter in Gmail that applies to multiple email addresses, it can be tedious to manually type out each address separated by ” OR “. Fortunately, Excel offers a couple of quick methods to streamline this process, whether you’re dealing with a few addresses or a lengthy list.
Table of contents
YouTube: Email address list with OR after (multiple emails in gmail filter)
Using Flash Fill for Quick Results
If you have a list of email addresses as in the image below, Flash Fill is an easy way to add the ” OR ” delimiter. As per the example below, start by entering your email addresses in column A. In column B, type the first email address exactly as it appears in column A, followed by a space, then a “OR,” and then another space. With the first cell filled in correctly, go to the Data tab and click on Flash Fill in the Data Tools group. Excel will automatically fill in the rest of the column, adding “OR” after each email address.
Once complete, you can copy the entire list from column B and paste it directly into your Gmail filter setup. Be sure to remove the last “OR” if it was added unnecessarily.
Creating a Permanent Solution with TEXTJOIN
For a more permanent solution that you can reuse with different lists of email addresses, the TEXTJOIN function in Excel is ideal.
Start by entering your list of email addresses in a single column. In a new cell, enter the following formula:=TEXTJOIN(” OR “, TRUE, A4:A52) (or use the Function Wizard as shown below).
This formula uses ” OR ” as the delimiter and joins all email addresses in the specified range. (A4:A52 in the example below).
When you press Enter, Excel will generate a single text string with all email addresses separated by ” OR ” and ready to be copied into Gmail. In the image below, you can see how the TEXTJOIN function consolidates the email addresses into a format ready for Gmail.
Tips and Troubleshooting
- Empty Cells: If your email list contains empty cells, make sure to include the “ignore empty” argument in the TEXTJOIN function to avoid unwanted spaces or “OR” separators in the final string. Alternatively if you want it to be clear that the data is missing type FALSE in this box.
- Updating the List: If you use the TEXTJOIN method and need to update your email list, simply replace the old addresses with new ones in the specified range, and Excel will automatically update the concatenated list. If the size of the list changes, remember to update the range in the TEXTJOIN function.