Edit Values for an IN Operator Search

In some cases, you may need to enter a large number of values for an "in" operator search. To make this easier, the add-in provides a Values editor you can use to create your comma-separated list.

Open the Values editor by clicking More Actions, then Edit Values from the Search prompt.

Description of the illustration edit-values-operator.png

The add-in displays the Values editor for the search condition.

Description of the illustration operator-values.png

Add, edit, or delete values directly in the Values box as necessary. Make sure you separate the values with a comma (,) and enclose string values in single quotes (' '). Do not enclose integer values.

You can also use the available icons above the Values box to fetch values from the clipboard or from an Excel range in your workbook.

Fetch Values from the Clipboard

Before you copy values from an application to the clipboard, make sure your values are on separate lines or are separated by tabs. You do not have to enclose string values in single quotes: the add-in will format them for you.

To fetch values from the clipboard, click the Fetch from the Clipboard () icon. If there are any existing values in the Values box, you'll be prompted to replace the existing values or add the copied values to the end of the set.

When you choose an option, the add-in fetches the values from the clipboard and formats them into a comma-separated list. Any empty values are discarded.

If the search condition is for a string field, the add-in encloses each value in single-quotes. If the search condition is for an integer field, the add-in tries to convert each value to an integer. If this conversion fails, the value is discarded.

Once the new values have been added, review the set to ensure it is formatted properly.

Fetch Values from an Excel Range

You can also fetch values from an Excel range address. Please note that if the range consists of multiple areas, only the first area is used.

Note:

Excel may automatically convert non-string values to other types. To suppress this behavior, set the cell format to "Text" in Excel's Format Cells dialog. Refer to your Excel documentation for more information.

To fetch values from an Excel range, click the Fetch from an Excel Range () icon, then provide an Excel range. Here are some examples of supported addresses:

Address	Refers to
Q20:Q45	A set of cells in the current worksheet in column Q
Sheet2!A1:A100	One hundred cells in Column A of Sheet2
Sheet3!K:K	The entire column K (about one million cells) in Sheet3
Sheet4:A1:D50	A two-dimensional range on Sheet 4 that includes 50 rows by 4 columns (200 cells)
MyData	A named range defined in your workbook.

Tip:

If you aren't sure about the correct form for a range of cells, you can define a named range and use the range name in the Excel Range Address property. To define a named range, select the range of cells you want, then right-click and select Define name. When defining the named range, make sure to select Workbook for the scope.

The add-in retrieves the values from those cells and formats them into a comma-separated values list. Empty cells are ignored. If the search condition is for a string field, the add-in treats each cell value as a string and encloses the values in single quotes. If it's for an integer field, the add-in converts each value to an integer. If this conversion fails, the value is discarded.