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

mh
Guest
Posts: n/a

 25th Jul 2009
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
Guest
Posts: n/a

 25th Jul 2009
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 <(E-Mail Removed)> 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
Guest
Posts: n/a

 25th Jul 2009
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
Guest
Posts: n/a

 25th Jul 2009
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
>

Ashish Mathur
Guest
Posts: n/a

 27th Jul 2009
Hi,

Now use the mconcat(range) function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mh" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

New Member
Join Date: Jan 2012
Posts: 2

 11th Jan 2012
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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Blake Microsoft Excel Misc 4 20th Oct 2009 05:51 AM jfcby Microsoft Excel Programming 3 13th Nov 2008 08:19 PM sumamahe_99@yahoo.com Microsoft Excel Misc 2 19th Oct 2006 01:19 PM =?Utf-8?B?Z3JhZHk4OA==?= Microsoft Excel Worksheet Functions 1 12th Oct 2005 08:03 PM Michael Tomasura Microsoft Excel Worksheet Functions 2 23rd Aug 2003 05:17 AM

Features