Concatenation and delimitter

[KIM W]

In building a string based on THREE (or FOUR maybe in the future) columns
using CONCATENATE I am putting a "+" between each columns values. Problem is
that sometimes a value is blank, and therefore I don't want extra "+" in the
result. Please help me get "I Want" string results without a hideous nested
if statement. It is acceptable to add columns which store interim values to
help build string-- this is not a seen workbook. The "I Want" values
ultimately go into a Pivot table along with some other columns.

COL A COL B COL C My Result I Want
OK?
------- ------- ------- ---------------------
--------------------- -----
RED WHITE BLUE RED+WHITE+BLUE RED+WHITE+BLUE Y
RED WHITE RED+WHITE+ RED+WHITE N
+WHITE+ WHITE
N
RED RED++ RED
N

 

[Max]

Data to be concat assumed in A2:C2 down
In D2: =SUBSTITUTE(TRIM(A2&" "&B2&" "&C2)," ","+")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[KIM W]

Almost works as I need. Sorry I omitted a piece of information relevant to
your solution:
My real data has spaces in the string contents of each cell, e.g. "RED
STRIPE", "WHITE STRIPE", etc.
Your handy formula puts "+" between every single word, not just between
every concatenated value.

 

[Gord Dibben]

This UDF will do what you need. Will ignore spaces in each cell and will
ignore empty 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

Usage is: =concatrange(A2:C2) entered in D2


Gord Dibben MS Excel MVP

 

[Teethless mama]

Try this:

=SUBSTITUTE(TRIM(CONCATENATE(A1," ",B1," ",C1))," stripe "," stripe+")

case sensitive, you may replace "stripe" with "STRIPE"

 

[KIM W]

Thanks! The UDF did it!

This UDF will do what you need. Will ignore spaces in each cell and will
ignore empty 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

Usage is: =concatrange(A2:C2) entered in D2


Gord Dibben MS Excel MVP