Concatenate cell range, separate with comma except for empty cells It is to my u

Question

Concatenate cell range, separate with comma except for empty cells

It is to my understanding that MoreFunc does not work with excel 2013 (or 2010 for that matter). And so I am looking to a formula similar to MCONCAT thatt will take the cell range C40:C57 and put the text that is is in each cell ad concantinate it, but seperatting them with commas. I currently am using =CONCATENATE(C40:C57) but that yields '1Tuna tin(s)2Brown ricce1Mixed Veg2Greek yogurt2Almonds1Porridge + milk1whey protein', and does not separate them with commass.

Thanks in advancee.

Explanation / Answer

You could use this custom function:

Function Concat(rng As Range, Optional sep As String = ",") As String
    Dim rngCell As Range
    Dim strResult As String
    For Each rngCell In rng
        If rngCell.Value <> "" Then
            strResult = strResult & sep & rngCell.Value
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(sep) + 1)
    End If
    Concat = strResult
End Function

Use like this:

=Concat(C40:C57)

If you store the function in a module in your personal macro workbook Personal.xlsb, so that it is available in all workbooks, use

=Personal.xlsb!Concat(C40:C57)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.