Combine text from multiple cells into one cell - =(A1&","&A2","&A3

  • Thread starter Thread starter mh
  • Start date Start date
M

mh

I would like to combine the words from different cells into one cell -
sometimes 1 or 2 cells are left empty

Example
A1 A2 A3 A4 A5
Peter Simon Derek Empty Kent

combine into one cell marking all
=A1&","&A2&","&A3&","&A4&","&A5

Should say
Peter, Simon, Derek, Kent (without extra , or space for empty cell included)

Could somebody help me with formula for this? would be greatly appreciated
 
This UDF ignores blank cells.

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

=concatrange(A1:A5)


Gord Dibben MS Excel MVP
 
Hi,

Here is a custom function that seems to do what you want:

Function myConCat(S As Range) As String
Dim cell As Range
Dim con As String
Dim I As Integer
I = 1
For Each cell In S
If I = 1 Then
con = cell
ElseIf cell <> "" Then
con = con & ", " & cell
End If
I = I + 1
Next cell
myConCat = con
End Function

so you would enter
=myConCat(A1:A10)
or use any other range.
 
Also, if I understand your question, you need to make a slight modification
of the other suggestion:

=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3&" "&A4&" "&A5)," ",", ")

note the extra space after the final comma.
 
I know this post is old, but I'd like to thank you guys for the great formulas. I have one mod I'm hoping someone can help me out with. Right now I'm using the following in VBE:


Function myConCat(S As Range) As String
Dim cell As Range
Dim con As String
Dim I As Integer
I = 1
For Each cell In S
If I = 1 Then
con = cell
ElseIf cell <> "" Then
con = con & ", " & cell
End If
I = I + 1
Next cell
myConCat = con
End Function


I'd like to be able to combine from 2 or 3 ranges into the same cell so I can exclude a few cells that are acting as headers withing the range. Any way to do this?

For instance I want cells C5:C11, C20:25 to all show up in the same cell and be separated by commas.
 
Back
Top