PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

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

 
Reply With Quote
 
 
 
 
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


 
Reply With Quote
 
 
 
 
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
>

 
Reply With Quote
 
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
>

 
Reply With Quote
 
Ashish Mathur
Guest
Posts: n/a
 
      27th Jul 2009
Hi,

You may download and install this addin -
http://www.download.com/Morefunc/300...-10423159.html

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
>

 
Reply With Quote
 
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to combine multiple columns into different cells in one column Blake Microsoft Excel Misc 4 20th Oct 2009 05:51 AM
Combine multiple cells into one cell. jfcby Microsoft Excel Programming 3 13th Nov 2008 08:19 PM
How to combine multiple rows of text cells into one cell sumamahe_99@yahoo.com Microsoft Excel Misc 2 19th Oct 2006 01:19 PM
Combine multiple cells into one cell range. =?Utf-8?B?Z3JhZHk4OA==?= Microsoft Excel Worksheet Functions 1 12th Oct 2005 08:03 PM
Combine multiple cells into one cell Michael Tomasura Microsoft Excel Worksheet Functions 2 23rd Aug 2003 05:17 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:25 AM.