Thread: =MultiCat removing commas from a string

I D/L this script for an excel concatenate problem I had, and it works exactly the way it says it should. It's function is to concatenate a list of cells (the =concatenate in Excel cannot do this for a range, only for identified cell refs, I have a column of over 300)

My problem is that many cells in my lists are empty and my output from =MULTICAT has strings of commas (the delimiters I chose) where the empty cells are, so a result will look like this :

,,,,,,,,,word1,,,,word2,,,word3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,word4,,,,,,,


I want it to read:

word1,word2,word3,word4


etc....
the format for MULTICAT is =MULTICAT(A1:a500,",")

Can someone suggest a way to either make multicat more appropriate for me, or a simple formula that will chop out the commas, except between words.

many thanks even if u just took the time to read and can't help.
Heres the MULTICAT code:
'*****************************************

  'Purpose: Concatenate all cells in a range
  'Inputs:  rRng - range to be concatenated
  '         sDelimiter - optional delimiter
  '            to insert between cell Texts
  'Returns: concatenated string
  '*****************************************
  Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
         MultiCat = MultiCat & sDelim & rCell.Text
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
  End Function

You need to select Go Advanced and click on the hash icon (#)

Public Function MultiCat( _
    ByRef rRng As Excel.Range, _
    Optional ByVal sDelim As String = "") _
        As String
Dim rCell As Range

For Each rCell In rRng
    If rCell.Value <> "" Then
        MultiCat = MultiCat & sDelim & rCell.Text
    End If
Next rCell

MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Regards