Error in Custom Function Since moving to Excel 2019

[scottb]

Hi everyone.
Hans, a while back you built a function for me that would concatenate a listed column range and place it in the current cell, separated by commas.

Code: Select all

Function ConcatCells(rng As Range) As String
    Dim cel As Range
    Dim retVal As String
    For Each cel In rng
        retVal = retVal & ", " & cel.Text
    Next cel
    If retVal <> "" Then
        ConcatCells = Mid(retVal, 3)
    End If
End Function

My work computer was upgraded to MS Office 2019 (32 bit).
The function still runs however it no longer separates the column values with a comma and a space. It just strings everything together.
Any idea why and how to resolve?
Thank you for your help!
-Scott

[HansV]

That's strange. It still works correctly for me in Excel 2019.

But you don't need this function anymore - Excel 2019 has a new built-in function TEXTJOIN. The formula

=TEXTJOIN(", ",TRUE, A1:A4)

can be used instead of

=ConcatCells(A1:A4)

S0466.png

[scottb]

Hi Hans,
TextJoin does the trick.
Thank you as always for your help.
-Scott

[ChrisGreaves]

TEXTJOIN. ...

And all over the world people lose hours of work time and sleep over why their budget spreadsheet has stopped working.

Or worse: They don't realize that this year's budget spreadsheet isn't working correctly!
I continue to be puzzled at MS's inability to cause such changes to go unannounced to the end-user.
I know that KB articles are published, but a #REF or #ERR or even a #UPGRADE in a budget spreadsheet would do wonders for the Bottom Line!

Cheers
Chris

[scottb]

Chris,
I was thinking about the very topic you described. Ugh....Good fun.
Be well everyone.