If you have a list of values in Microsoft Excel and need to create an IN clause for a WHERE statement in SQL Server or for the criteria field in Microsoft Access. We have the perfect formula and VBA code for this.

Formula to create an IN Clause for SQL Server and Microsoft Access

The following formula creates an IN Clause for the values in cells A2 to A6.

="IN("&TEXTJOIN(", ",TRUE,"'"&A2:A6&"'")&")"

A breakdown of the formula which creates an IN Clause

="IN("

This part of the formula places the IN string and open parenthesis in double quotes, because strings in Excel formulas must be placed in double quotes.

&

The & (ampersand) operator concatenates two values together to produce a continuous text string.

TEXTJOIN(", ",TRUE,"'"&A2:A6&"'")

The TEXTJOIN function concatenates a range of values together which are separated by a delimiter. The first argument allows you to specify the delimiter. In our formula, the delimiter is a comma followed by a space. The second argument determines whether empty cells should be included in the list. In our formula, we have chosen TRUE which tells the TEXTJOIN function to ignore empty cells. The third argument specifies the range of cells to be concatenated. In our formula, the range of cells to be concatenated are cells A2 to A6. The values in the cells need to be surrounded by single quotes for our IN Clause, as a result, single quotes are placed in double quotes before and after the range of cells to be concatenated.

&")"

The final part of our formula concatenates the close parenthesis to rest of the formula.

VBA code to quickly create an IN Clause for SQL Server and Microsoft Access

If you regularly create IN statements for your database queries, then we recommend using our VBA code below to automate the process. Our VBA code creates an IN Statement for a selection of cells and copies the IN Statement to your clipboard so that it’s ready to be pasted into your database. We recommend adding the VBA code to your Personal Macro Workbook and adding the macro to your quick access toolbar for easy access.