Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MH MH is offline
external usenet poster
 
Posts: 28
Default Combine text from multiple cells into one cell - =(A1&","&A2","&A3

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Combine text from multiple cells into one cell - =(A1&","&A2","&A3


Try:

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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119531

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Combine text from multiple cells into one cell - =(A1&","&A2","&A3

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Combine text from multiple cells into one cell - =(A1&","&A2","&A3

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Combine text from multiple cells into one cell - =(A1&","&A2","&A3

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" wrote in message
...
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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
change "true" and "false" to "availble" and "out of stock" inthestands Excel Worksheet Functions 2 July 19th 07 07:05 PM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
Insert "-" in text "1234567890" to have a output like this"123-456-7890" Alwyn Excel Discussion (Misc queries) 3 October 25th 05 11:36 PM


All times are GMT +1. The time now is 12:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"