Experts Exchange connects you with the people and services you need so you can get back to work.
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
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
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
aconcat = aconcat & a & sep
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
Open in new window
=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
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
If bSorted = True Then Exit For
For i = 1 To n
s = s & sep & vSorted(i)
ConcatInOrder = Mid$(s, Len(sep) + 1)
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
Your question, your audience. Choose who sees your identity—and your question—with question security.