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

  • 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.



    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 :(

  • 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)

  • 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!


  • 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?


  • 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.

  • 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?


  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!