How to create an IN Clause for your database from a list in Excel using a formula or VBA code

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.

Sub Create_IN_Statement_String()

    Dim cell_column_number As Integer
    Dim cell_row_number As Integer
    Dim cell_value As Variant
    Dim in_statement As String
    Dim cell As Range
    
    in_statement = "IN ('"
    
    For Each cell In Selection
    
        cell_column_number = cell.Column
        cell_row_number = cell.Row
        
        cell_value = cell.Value
        
        'Trim the value in the current cell
        cell.Value = Trim(cell_value)
        
        'Add an apostrophe, comma, space and another apostrophe
        cell_value = cell_value & "', '"
        in_statement = in_statement & cell_value
        
    Next
    
    'remove the final comma, space and apostophe
    
    in_statement = Left(in_statement, Len(in_statement) - 3) & ")"
    
    'Paste IN statement one row below the last row of the selected cells
    
    Cells(cell_row_number + 1, cell_column_number) = in_statement
    
    'Copy IN statement to clipboard
    
    Cells(cell_row_number + 1, cell_column_number).Copy

End Sub