VBA Function to do the job of TEXTJOIN gives extra commas

[Chris G]

[Excel VERSION without the TEXTJOIN function]

I built a highly automated timesheet template. It uses a macro-enable template, plus a timesheet template, and has a custom tab on the ribbon with a few vital functions.

I have a column with joins the date and the number of hours taken in sick leave for a particular day, i.e on that row of data, and adds "hrs" to the number of hours.

An example of the combined values is:

0.25 hrs 25-12-23 [Non-USA date format]

If there is more than one date on which the person records sick leave for the two week pay period, TEXTJOIN would easily concatenate the values in this column at the bottom of the column, and delimit each with a comma.

Because the user does not have TEXTJOIN in their version of Excel, I use the following Function in the macro-enabled template to do the same job:

Function TEXTJOINED(delimiter As String, ignore_empty As Boolean, mg As Range) As String
' https://www.youtube.com/watch?v=sWeXtPB7y9o
Dim compiled As String

    For Each cell In mg
        If ignore_empty And IsEmpty(cell.value) Then
        'nothing
    
        Else
            compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.value)
        End If
    Next

    TEXTJOINED = compiled

End Function

In the template I added

=TEXTJOINED(", ",TRUE,V9:V24)



to give me the concatenation of values in the column. (This data is then transferred to another automated worksheet).


This gives me almost what I want, but adds a string of commas every time:

0.25 hrs 25-12-23, 0.5 hrs 26-12-23, , , , , , , , , , ,

Not having worked with Excel VBA, any tips on how to exclude those commas would be most welcome.

Thanks.

[p45cal]

Probably because the cells which appear blank are not in fact balnk. Check for a space or anything else in those cells.
If you have a formula in those blank cells which may result in the cell containing an empty string (eg. =IF(A1>10,10,"") ) then commas will show up.
Instead of checking for Empty, check the length of the cell contents. If at the same time you also want to exclude cells which have only a space or spaces in, then change the line:

If ignore_empty And IsEmpty(cell.Value) Then

to:

If ignore_empty And Len(Application.Trim(cell.Value)) = 0 Then

[Chris G]

Thanks p45cal, that is the solution. There is indeed a formula in each of the cells being concatenated, e.g. =IF(O20<>0,O20&" hrs " &TEXT(B20,"d-mm-yy"),""), so they look empty when there's no data to display.

So thank you very much indeed for the solution!