Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
I'm trying to set up a formula which will contactenate the contents of 6 or 7
cells. I would like to be able to skip any blank values. Using the following formula: =a1&", "&b1&", "c1&", "&d1&", "&e1&", "&f1 Assuming cell b1, d1, and e1 were left blank, I would get the following: Ann, , Cathy, , , Frank What I want to see is: Ann, Cathy, Frank Can anyone give me any ideas on how to go about this? Soma104 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
Are all the values in A1:F1 single words--no spaces?
If yes: =SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ") soma104 wrote: I'm trying to set up a formula which will contactenate the contents of 6 or 7 cells. I would like to be able to skip any blank values. Using the following formula: =a1&", "&b1&", "c1&", "&d1&", "&e1&", "&f1 Assuming cell b1, d1, and e1 were left blank, I would get the following: Ann, , Cathy, , , Frank What I want to see is: Ann, Cathy, Frank Can anyone give me any ideas on how to go about this? Soma104 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
On Mon, 29 May 2006 12:29:01 -0700, soma104
wrote: I'm trying to set up a formula which will contactenate the contents of 6 or 7 cells. I would like to be able to skip any blank values. Using the following formula: =a1&", "&b1&", "c1&", "&d1&", "&e1&", "&f1 Assuming cell b1, d1, and e1 were left blank, I would get the following: Ann, , Cathy, , , Frank What I want to see is: Ann, Cathy, Frank Can anyone give me any ideas on how to go about this? Soma104 You could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr. Then use the formula: =MCONCAT(A1:A6,", ") OR you could use a UDF. <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. Use the formula: =ConcatNonBlanks(A1:A6,", ") =========================== Function ConcatNonBlanks(rg As Range, Optional Separator As String) As String Dim c As Range For Each c In rg If Len(c.Text) 0 Then ConcatNonBlanks = ConcatNonBlanks & c.Text & Separator End If Next c 'remove last separator If Not IsEmpty(Separator) Then ConcatNonBlanks = Left(ConcatNonBlanks, _ Len(ConcatNonBlanks) - Len(Separator)) End If End Function ============================== --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
On Mon, 29 May 2006 14:43:02 -0500, Dave Peterson
wrote: Are all the values in A1:F1 single words--no spaces? If yes: =SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ") Neat solution for single word entries! --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
I think I'd use a UDF, too. But that formula could be modified for cells with
multiple words: =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ",CHAR(1)) &" "&SUBSTITUTE(B1," ",CHAR(1)) &" "&SUBSTITUTE(C1," ",CHAR(1)) &" "&SUBSTITUTE(D1," ",CHAR(1)) &" "&SUBSTITUTE(E1," ",CHAR(1)) &" "&SUBSTITUTE(F1," ",CHAR(1)))," ",", "),CHAR(1)," ") But it gets kind of ugly kind of fast. Ron Rosenfeld wrote: On Mon, 29 May 2006 14:43:02 -0500, Dave Peterson wrote: Are all the values in A1:F1 single words--no spaces? If yes: =SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ") Neat solution for single word entries! --ron -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
On Mon, 29 May 2006 19:30:03 -0500, Dave Peterson
wrote: I think I'd use a UDF, too. But that formula could be modified for cells with multiple words: =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ",CHAR(1)) &" "&SUBSTITUTE(B1," ",CHAR(1)) &" "&SUBSTITUTE(C1," ",CHAR(1)) &" "&SUBSTITUTE(D1," ",CHAR(1)) &" "&SUBSTITUTE(E1," ",CHAR(1)) &" "&SUBSTITUTE(F1," ",CHAR(1)))," ",", "),CHAR(1)," ") But it gets kind of ugly kind of fast. UDF's are certainly more flexible. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation and skipping blank cells
On Mon, 29 May 2006 15:50:40 -0400, Ron Rosenfeld
wrote: On Mon, 29 May 2006 12:29:01 -0700, soma104 wrote: I'm trying to set up a formula which will contactenate the contents of 6 or 7 cells. I would like to be able to skip any blank values. Using the following formula: =a1&", "&b1&", "c1&", "&d1&", "&e1&", "&f1 Assuming cell b1, d1, and e1 were left blank, I would get the following: Ann, , Cathy, , , Frank What I want to see is: Ann, Cathy, Frank Can anyone give me any ideas on how to go about this? Soma104 You could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr. Then use the formula: =MCONCAT(A1:A6,", ") OR you could use a UDF. <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. Use the formula: =ConcatNonBlanks(A1:A6,", ") =========================== Function ConcatNonBlanks(rg As Range, Optional Separator As String) As String Dim c As Range For Each c In rg If Len(c.Text) 0 Then ConcatNonBlanks = ConcatNonBlanks & c.Text & Separator End If Next c 'remove last separator If Not IsEmpty(Separator) Then ConcatNonBlanks = Left(ConcatNonBlanks, _ Len(ConcatNonBlanks) - Len(Separator)) End If End Function ============================== --ron Some testing reveals that we can eliminate testing for the use of Separator, so the routine simplifies a bit: ============================ Function ConcatNonBlanks(rg As Range, Optional Separator As String) As String Dim c As Range For Each c In rg If Len(c.Text) 0 Then ConcatNonBlanks = ConcatNonBlanks & c.Text & Separator End If Next c 'remove last separator ConcatNonBlanks = Left(ConcatNonBlanks, _ Len(ConcatNonBlanks) - Len(Separator)) End Function ======================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|