UDF CONCATENATE with Separator - Empty Cells return "0" Result

EgoProwler · Aug 5th 2012

I'm using a UDF from a previous post (http://www.ozgrid.com/forum/showthread.php?t=70327) that works as expected except when the concatenated cell range is blank. I'm admittedly over my head when it comes to VBA and I'm getting nowhere fast determining why the cell containing the UDF has a result of '0' when the range is blank -- I need the result to be blank.

Code

Function ConcatenateRange(Parts As Range, Separator As String)     ' Build a single string from a passed range with a
     ' passed separator between each value
    Dim strTemp, sepTemp As String
    Dim cel As Range
    Dim cnt As Integer
    strTemp = ""
    For Each cel In Parts.Cells
        If cel.Value = "" Or cel.Value = 0 Then
            sepTemp = ""
        Else
            sepTemp = Separator
        End If
        If Len(strTemp) = 0 Then
            strTemp = cel.Value
        Else
            strTemp = strTemp & sepTemp & cel.Value
            End If
        Next cel
        ConcatenateRange = strTemp
    End Function

Display More

Changing the Number Format of the cell doesn't seem to make any difference in the final result. Not that it should, I was just taking a stab in the dark this was somehow related to my results.

My separator as defined in the reference cell is a period, =ConcatenateRange(I6:L6,"."). I see no place in this Function to the best of my interpretation results in a '0', rather than a blank cell.

If I understand correctly, if the cel.Value = "" (empty) OR cel.Value = 0 then the result should be empty or the separator defined by ".".

I'm stumped

**mikerickson** · Aug 5th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

try this.
The "As String" has to be added to the declaration, because as written strTemp was dimensioned as Variant (which interprets a blank cell as 0)

Code

Dim strTemp As String, sepTemp As String


'...


If Len(strTemp) = 0 Then 
    strTemp = CStr(cel.Value)
Else 
    strTemp = strTemp & sepTemp & CStr(cel.Value)
End If

Display More

EgoProwler · Aug 5th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

That did the trick! Thank you much, mikerickson! Learned something new today!

EgoProwler · Aug 6th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

I'd like to confirm my understanding of what this UDF is doing. I commented each line with what I believe is occurring at each line. The only thing I can't seem to get my head around is how it steps through each cell so you don't keep looping through the range. Is the loop count equal to the size of the range then? So if the range is 4 cells in size, the For Each loop will end at the last cell in the range and process the ConcatenateRange line of the script? Thanks in advance!

Code

Dim strTemp As String, sepTemp As String
    ' declare strTemp and sepTemp as Strings. A String is a data type.
    
    Dim cell As Range
    ' declares cell as variable to store a cell reference




    Dim cnt As Integer
    ' declares variable cnt and stores result as an Integer value




    strTemp = ""
    ' defines the String strTemp as blank




    For Each cell In Parts.Cells
        If cell.Value = "" Or cell.Value = 0 Then
        ' If value of cell is blank or = 0
            sepTemp = ""
            ' the variable sepTemp will be stored as blank
        Else
        'If value of cell is not blank or = 0
            sepTemp = Separator
            ' the variable sepTemp will store the separator defined by the user
        End If
        If Len(strTemp) = 0 Then
        ' if the length of the variable stored in strTemp = 0
            strTemp = CStr(cell.Value)
            ' the variable strTemp will be equal to the value of the current cell.
        Else
        ' if the stored variable strTemp is not equal to 0
            strTemp = strTemp & sepTemp & CStr(cell.Value)
            ' the variable strTemp will equal the current cell value and concatenate with the user defined separator
            End If
        Next cell
        ConcatenateRange = strTemp
    End Function

Display More

steveorg · Aug 6th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

After I first developed the UDF, I added the option to include empty cells or cells with a value of 0 in the concatenation. Accordingly, there are two new arguments in the function, which are explained in the code comments.

EgoProwler: "For Each cell' defines the length of the loop. If I didn't understand your question properly, I'm not sure it still applies since the logic is a little different.

mikerickson: Is there still a purpose to CStr?

Code

Function ConcatenateRange(Parts As Range, Separator As String, Blank As Integer, Zero As Integer)
    ' Build a single string from a passed range with a passed separator
    ' placed between each value. The separtaor can be more than 1 character
    ' If Blank = 0, cells = "" are ignored, else they concatenated
    ' If Zero = 0, cells = 0 and <> "" are ignored, else they concatenated
    Dim strAll As String, cel As Range, celCount As Integer
    strAll = ""
    celCount = 0
    For Each cel In Parts.Cells
        If (Blank = 0 And cel.Value = "") Or (Zero = 0 And cel.Value = 0 And Len(cel) > 0) Then
            If celCount = 1 Then
                celCount = celCount + 1
            End If
        Else
            celCount = celCount + 1
            strAll = strAll & Separator & cel.Value
        ' Removes separator placed before first concatenated cell
            If celCount = 1 Then
                strAll = Right(strAll, Len(strAll) - Len(Separator))
            End If
        End If
        Next cel
    ConcatenateRange = strAll
End Function

Display More

EgoProwler · Aug 6th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

Quote from steveorg;621703

EgoProwler: "For Each cell' defines the length of the loop. If I didn't understand your question properly, I'm not sure it still applies since the logic is a little different.

Thanks, stevorg. I think you answered the question and are confirming what I thought - that the size of the defined range represents the length of the loop in the "For Each cell" loop.

steveorg · Aug 6th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

I've removed more unnecessary code. Now there is no point to the part of the IF statement that is before ELSE, however I can't figure out how to negate the logic in the IF. Everything that I've tried has yielded bad results. Anybody have thoughts of how to fix it and get rid of the unnecessary ELSE?

Code

Function ConcatenateRange(Parts As Range, Separator As String, Blank As Integer, Zero As Integer)
    ' Build a single string from a passed range with a passed separator
    ' placed between each value. The separtaor can be more than 1 character
    ' If Blank = 0, cells = "" are ignored, else they are concatenated
    ' If Zero = 0, cells = 0 and <> "" are ignored, else they are concatenated
    Dim strAll As String, cel As Range, celCount As Integer
    strAll = ""
    celCount = 0
    For Each cel In Parts.Cells
        If (Blank = 0 And cel.Value = "") Or (Zero = 0 And cel.Value = 0 And Len(cel) > 0) Then
        Else
            celCount = celCount + 1
            strAll = strAll & Separator & cel.Value
        ' Removes separator placed before first concatenated cell
            If celCount = 1 Then
                strAll = Right(strAll, Len(strAll) - Len(Separator))
            End If
        End If
        Next cel
    ConcatenateRange = strAll
End Function

Display More

**mikerickson** · Aug 6th 2012

Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

Here is a similar UDF that cocatenates similar to SUMIF.
To return a comma delimited string of all in a range a formula like
=ConcatIf(A1:A10, "<>", A1:A10, ",") would be used.

The CStr coerces the value in the cell to a string, so this UDF will not crash if a cell holds an error value like #DIV/0

Code

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
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    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))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

Display More

UDF CONCATENATE with Separator - Empty Cells return "0" Result

Participate now!

Share