Microsoft Office Forums - View Single Post - VBA Function to do the job of TEXTJOIN gives extra commas

Chris G · 12-19-2024, 02:44 PM

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

Code:

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.