I’ve been playing around with the new-to-2016 TextJoin function. My first use was to concatenate a bunch of cells for a comma-delimited parameter. TEXTJOIN works way better than the near-useless CONCATENATE function of yesterversions (and I can now drop Rick Rothstein’s very nice ConCat function from my personal macro workbook). One great TEXTJOIN feature is the ability to ignore blank cells in an input range. Another is that you can have multi-character delimiters, including characters like the CHAR(10) linefeed.
This makes it ideal for a type of utility used by many of us data wranglers: one that takes a column of values and formats it for use in a SQL IN clause:
The formula above is
It’s for text values, so it wraps everything in single-quotes. If you were using it for numbers you’d remove these. It also includes a comma in the delimiter. And, my favorite part, it includes a linefeed to format the words in a one-word-per-row column. The beginning and the end of the formula simply add the starting and ending single-quotes.
In the picture above I have word wrap turned on to show the formatting, but you can turn it off and it will still paste into separate rows:
You could take it two steps further and add the “IN” and opening and closing parentheses. My main goal though is to avoid the repetitive comma and single-quote part though. I’ll do it by hand for up to about 10 items but after that I want a formula like this.
A FormatForSqlList UDF
Of course, it would be really nice to have this as a UDF in my toolkit. Doing so would let me spiff it up a bit:
Optional ListIsText As Boolean = True) As String
If Val(Application.Version) < 16 Then
FormatForSqlList = "requires Excel 2016 or higher"
Exit Function
End If
FormatForSqlList = "(" & vbCrLf & IIf(ListIsText, "'", "") & _
WorksheetFunction.TextJoin(IIf(ListIsText, "'", "") & "," & _
vbCrLf & IIf(ListIsText, "'", ""), True, ListRange) & _
IIf(ListIsText, "'", "") & vbCrLf & ")"
End Function
I don’t write many UDFs, so the above could probably use some refinement. I guess it would be nice if it took values directly instead of just from a range, but maybe not. My normal pattern is that I’m taking a bunch of results from a query in SQL Assistant or in Excel, and those both lend themselves well to just pasting into a column of cells.
The Double-Quote Problem
Unfortunately, both formulas have an unwanted side-effect. When you copy and paste from a one-cell comma-separated list with linefeeds to a text editor or SQL IDE, double-quotes are added at the start and end of the string. One solution is to paste the string to Word and then into the text editor, but that seems more cumbersome than just deleting the double-quotes. I assume I could do something with pasting to the Windows clipboard via a DataObject, but then I’d need to have a separate subroutine or maybe a userform. Those also seem clunky, so I’ll just see how much it bugs me. If you come up with a solution, please let us know.
Another Interesting and Way Fancier TEXTJOIN Function
Chris Webb has a nice blog post here about finding all selected items in a slicer using TEXTJOIN.
You mention the range A:A, this works fine when it is a range. But what about when it is a table? Using A:A will also pick up the header too. Is there a way to avoid this?
=CLEAN(TEXTJOIN("’," & CHAR(10) & "’",TRUE,A2:A5000) & "’")
removes the leading and trailing double quotes
Apparently I’m still getting comments on this blog! Sorry I didn’t notice that until today. Drew. I’m going to try your suggestion, thanks!