Solve your biggest tech problems alongside global tech experts with 1:1 help.
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
=aconcat(SMALL(TRIM(MID(SUBSTITUTE(A9,",",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A9)-LEN(SUBSTITUTE(A9,",",""))+1))-1)),100))+0,ROW(INDIRECT("1:"&LEN(A9)-LEN(SUBSTITUTE(A9,",",""))+1))),", ")
Function ConcatInOrder(ParamArray vData() As Variant) As String
Dim v As Variant, vSorted As Variant
Dim i As Long, j As Long, n As Long
Dim bSorted As Boolean
Dim s As String, sep As String
sep = "," 'Separator between items in list
ReDim vSorted(1 To UBound(vData))
For Each v In vData
If v <> "" Then
n = n + 1
vSorted(n) = v
End If
Next
If n > 1 Then
For i = 1 To n
bSorted = True
For j = 2 To n
If vSorted(j) < vSorted(j - 1) Then
v = vSorted(j - 1)
vSorted(j - 1) = vSorted(j)
vSorted(j) = v
bSorted = False
End If
Next
If bSorted = True Then Exit For
Next
End If
For i = 1 To n
s = s & sep & vSorted(i)
Next
ConcatInOrder = Mid$(s, Len(sep) + 1)
End Function