User defined formula - concatenate range

[Steve D]

I apologize if this is too lengthy. I am using a User defined formula to
take a range of values in the workbook and concatenate them into one long
string value . I also want them to be separated by commas.

Example (real data is much larger):
A1: 011800
A2: 121801
A3: 051810
A4:
A5:

I would like to use a formula in B1 that would get this result: 011800,
121801, 051810

I have been able to accomplish this to an extent with the following user
defined formula:
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

But I am getting this result: 011800, 121801, 051810, , ,

I get a comma for each blank cell. How can I get around this?

Steve

 

[Bob Phillips]

Where's the initial announcement <G>?

Public Function MultiCat(ByRef rRng As Range, _
Optional ByVal sDelim As String = "") As String
Dim rCell As Range
For Each rCell In rRng
If rCell.Value <> "" Then
MultiCat = MultiCat & sDelim & rCell.Text
End If
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

 

[David McRitchie]

Hi Steve,
You are correctly creating the comma at the beginning an the
getting rid of the leading comma when finished. Your problem
is that your selection contains empty cells.

you can limit to cells with content
For Each rCell In SpecialCells(rRng, xlTextValues)

you can additionally check that the value is not an empty string
If rCell.Text <> "" Then


Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng.SpecialCells(xlConstants, xlTextValues)
If rCell.Text <> "" Then
MultiCat = MultiCat & sDelim & rCell.Text
End If
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


=multicat(A22,", ")
=multicat(A22)
=multicat(A22,",")





--

 

[Harlan Grove]

Steve D wrote...
....

Example (real data is much larger):
A1: 011800
A2: 121801
A3: 051810
A4:
A5:

I would like to use a formula in B1 that would get this result: 011800,
121801, 051810

I have been able to accomplish this to an extent with the following user
defined formula:

The term is 'function'. Formulas call functions.

Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _ ....
But I am getting this result: 011800, 121801, 051810, , ,

I get a comma for each blank cell. How can I get around this?

This is a common problem. The best solution is NOT to use an optional
argument for delimiters. Dispensing with the optional argument would
also allow you to make the sole argument a ParamArray, which would
allow you to pass variable numbers of arguments to the function.

One example is the mcat function in

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/94456a9e326b19a6

It could be used in array formulas like

=SUBSTITUTE(TRIM(mcat(A1:A5&" "))," ",", ")

which, with your data, returns

011800, 121801, 051810

If you insist on using a single udf, then add a second optional
argument to specify whether or not to include blank fields. Probably
best to default it to FALSE to exclude blank fields. Something like


Function smcat( _
a As Variant, _
Optional s As String = "", _
Optional ibf As Boolean = False _
) As String
'----------------------------------
Dim x As Variant

If IsArray(a) Then
For Each x In a
If (x <> "") Or ibf Then smcat = smcat & s & x
Next x
smcat = Mid(smcat, Len(s) + 1)

Else
smcat = a

End If
End Function


It's a reasonable bet that someday you'll need to include the blank
fields, and it's a bad idea to have another function to include them
when a single function can include or exclude.