Dim Cell As Range, IDs As String
For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants, xlNumbers)
If Not Cell.Value Like "*[!0-9]*" Then IDs = IDs & "," & Cell.Value
Range("B2").Value = "'(" & Mid(IDs, 2) & ")"
Yes, I find it easier to do put the commas in first because mid is so simple to implement for removing the first character (especially since its third argument is optional defaulting to the remainder of the text when omitted)... putting the comma in last requires a combination of Left and Len (minus the length of the delimiter) and is more awkward in my opinion.
It's a double negative (making a positive). If you tried to test it this way...
If Text Like "*[0-9]*" Then
all that would tell you is that at least one digit was in the text... it would say nothing about the other characters. Doing this...
If Text Like "*[!0-9]*" Then
would evaluate as true if at least one character was not a digit (that is what the exclamation point does... matches all characters not in the list), but that is not what you want the Then block to execute for... you want it to execute if none of the characters in Text are non-digits, to you apply the Not operator to get that as the True result.
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
Dim i As Long, j As Long
Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
stringsRange.Column - compareRange.Column)
For i = 1 To compareRange.Rows.Count
For j = 1 To compareRange.Columns.Count
If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
I believe I understand now. I thought it may have to do with something like a double negative.... but I was unable to see the second negative (!). Therefore, I was having trouble comprehending the solution. Thanks for the very thorough explanation! I appreciate you taking the time to explain the solution