merge concatenate

J

jmshramm

What's the shortcut to use in a merge or concatenate formula when there's
hundreds of cells I want to merge into a single cell, separated by a comma,
without having to type in each cell name into the formula? I have data in A1
through A230.
 
K

Ken Wright

Posted I think by J E McGimpsey

Public Function MultiCat(ByRef rRng As Excel.Range, _
Optional ByVal sDelimiter As String = "") As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelimiter & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, 1 - (Len(sDelimiter) > 0))

'Call as =MultiCat(A1:A5)
'you can add a delimiter if you wish:
' =MultiCat(A1:A5,",")

End Function
 
G

Gord Dibben

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Gord Dibben Excel MVP
 
Top