VBA Function/ Code – Convert Dynamic Column of Data into Text String

I’m creating a tool where users can query an ODBC through excel. I need a function which will look at a column of item id’s which the user has pasted in and then create a text string which can be used in the Sql part of the VBA. The number of item Id’s can vary.

Using a similar function (not created by myself), I can reference one cell and insert the ‘ and , where needed but I can’t seem to get this to work for multiple cells in one column.

‘Here is the code I have used for the existing function:

                    Function CreateSQLAndQry(field_input As String, input_string As String) As String
                    cleaned_string = Replace(input_string, " ", "")
                    Dim xArray() As String
                    xArray() = Split(cleaned_string, ",")
                    Dim lenArray As Integer
                    lenArray = UBound(xArray())
                    Dim new_string As String
                    new_string = ""
                    For I = 0 To lenArray
                        xArray(I) = "'" & xArray(I) & "'"
                        new_string = xArray(I) & "," & new_string
                    Next
                    If input_string = "" Then
                    new_string = ""
                    Else: new_string = Left(new_string, Len(new_string) - 1)
                    End If

                    If input_string = "" Then
                    new_qry = ""
                    Else: new_qry = " AND " & field_input & " IN (" & new_string & ") "
                    End If

If the column of data was to look like this:

test1 test2

I need the output to look like this:

in (‘test2’, ‘test1’)

Answer

The formula you have is incomplete, it doesn’t show the ending, but here’s a function that loops through any amount of cells and constructs a string that looks similar to what you’re looking for. You can change the prefix in the constants at the top of the formula or tinker with it to get whatever manipulations you want done.

Function PullTextTogether(rng As Range) As String
Const xSepx As String = ","
Const preText As String = "in "
Const ignoreText As String = " " ''characters you want stripped out

Dim aCell As Range

For Each aCell In Intersect(rng, rng.Worksheet.UsedRange).Cells

    If aCell.Value2 <> "" Then
    PullTextTogether = PullTextTogether & "'" & xSepx & "'" & aCell.Value2

    'Duplicate this with more text you would want to strip out
    PullTextTogether = Replace(PullTextTogether, ignoreText, "")

    End If

Next aCell

If PullTextTogether <> "" Then
    'cleans up
    PullTextTogether = preText & "('" & Right(PullTextTogether, Len(PullTextTogether) - 3) & "')"
End If

End Function