Combine text from multiple cells into one cell - =(A1&","&A2","&A3
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.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"mh" wrote:
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
|