Concatenation and delimitter

  • Thread starter Thread starter KIM W
  • Start date Start date
K

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
 
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.
 
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
 
Try this:

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

case sensitive, you may replace "stripe" with "STRIPE"
 
Thanks! The UDF did it!

Gord Dibben said:
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
Click to expand...
 
Back
Top