Concatenate cells and adding + sign, but skip if cell is blank.

[scottwland]

I need to concatenate cells with a plus sign added in-between each of the cells content. However if some of the cells are blank I don't want the extra + signs added from each blank cell. I assume I need and If Then statement


My formula looks like this
=B1&"+"&C1&"+"&D1&"+"&E1&"+"&F1

My output looks like this with all fields containing a value.
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="153"> <col style="" width="153"> <tbody><tr height="13"> <td height="13" width="153">b1+c1+d1+e1+f1</td> </tr> </tbody></table>
But this is what it looks like when I have blank fields
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="153"> <col style="" width="153"> <tbody><tr height="13"> <td height="13" width="153">b2+c2+++f2</td> </tr> </tbody></table>
I need to eliminate the 2 extra + signs in the output.

Thanks for any help. This is a little over my formula building head.

Scott

 

[mikerickson]

How about

=SUBSTITUTE(TRIM(B1&" "&C1&" "&D1&" "&E1&" "&F1), " ", "+")

 

[VoG]

Maybe a UDF

Function concat(r As Range) As String
Dim c As Range
For Each c In r
    If c.Value <> "" Then concat = concat & c.Value & "+"
Next c
If concat <> "" Then concat = Left(concat, Len(concat) - 1)
End Function

 

[scottwland]

Thanks Mike that worked very well. I will also try your idea VoG.

Scott

 

[scottwland]

Hi Mike

I just found an issue with the formula you helped me with in June. One of the fields that is being concatenated is a zip code field and if the zip code starts with a zero, the zero is left out when the formula is generated. I have tried to change the formatting of the field to text field and the zero still disappears. Do you know what i need to do to keep the digit?

Thank you

Scott

 

[mikerickson]

One way might be to format the source cells A1, B1,...,F1 as text.