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

Discussion in 'Microsoft Excel Worksheet Functions' started by mh, Jul 25, 2009.

1. ### mhGuest

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

mh, Jul 25, 2009

2. ### Gord DibbenGuest

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

On Sat, 25 Jul 2009 10:11:01 -0700, 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

Gord Dibben, Jul 25, 2009

3. ### Shane DevenshireGuest

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
>

Shane Devenshire, Jul 25, 2009
4. ### Shane DevenshireGuest

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.

--
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
>

Shane Devenshire, Jul 25, 2009
5. ### Ashish MathurGuest

Hi,

Now use the mconcat(range) function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mh" <> wrote in message
news:...
> 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
>

Ashish Mathur, Jul 27, 2009
6. ### CavProd

Joined:
Jan 11, 2012
0
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.

CavProd, Jan 11, 2012