handling nulls in concatenated string

[df]

I have 3 columns of data I need to concatenate and I want
to prefix the data with a "label". Current code is
=CONCATENATE("Goal:" & B17 & ",","Note:" & C17
& ",","Size:" & D17). How do I prevent the labels (Goal,
Note, and Size) and commas from appearing if the
corresponding columns (B17, C17, D17) are empty?

 

[Frank Kabel]

Hi
a kludgy way:
=LEFT(IF(B17<>"","Goal:" & B17 & ",","") & IF(C17<>"","Note:" & C17 &
",","") & IF(D17<>"","Size:" & D17 & ",",""),LEN(IF(B17<>"","Goal:" &
B17 & ",","") & IF(C17<>"","Note:" & C17 & ",","") & IF(D17<>"","Size:"
& D17 & ",",""))-1)

 

[Sandy Mann]

Or a shorter kludgy:
=IF(B17<>"","Goal:"&B17&IF(OR(C17,D17<>""),",",""),"")&IF(C17<>"","Note:"&C1
7&IF(D17<>"",",",""),"")&IF(D17<>"","Size:"&D17,"")


Sandy

 

[hgrove]

Frank Kabel wrote...

a kludgy way:
=LEFT(IF(B17<>"","Goal:" & B17 & ",","") & IF(C17<>"","Note:" & C1 &",","")
& IF(D17<>"","Size:" & D17 & ",",""),LEN(IF(B17<>"","Goal:" & B17 ",","")
& IF(C17<>"","Note:" & C17 & ",","") & IF(D17<>"","Size:" & D17

",",""))-1)
...

Yuck!

Some day you'll learn the value of MID.

=MID(IF(B17<>"",",Goal:"&B17,"")&IF(C17<>"",",Note:"&C17,"")
&IF(D17<>"",",Size:"&D17,""),2,1024

 

[Frank Kabel]

Frank Kabel wrote...
",",""))-1)
..

Yuck!

Some day you'll learn the value of MID.

=MID(IF(B17<>"",",Goal:"&B17,"")&IF(C17<>"",",Note:"&C17,"")
&IF(D17<>"",",Size:"&D17,""),2,1024)

lol
indeed I should have thought about that
thanks for this
Frank

 

[Dana DeLouis]

I have 3 columns of data

With a lot of data, it could look messy. If you would like to try something
a little different...
Add 3 Range named formulas to your sheet. The following Macro will do that
for you. This assumes however, that your CONCATENATE function is going into
Column E. Adjust the values inside the "[ ]" to adjust.

Sub Names_Goal_Note_Size()
ActiveWorkbook.Names.Add Name:="Goal", RefersToR1C1:= _
"=IF(Sheet1!RC[-3]="""","""",""Goal: "")"

ActiveWorkbook.Names.Add Name:="Note", RefersToR1C1:= _
"=IF(Sheet1!RC[-2]="""","""","" Note: "")"

ActiveWorkbook.Names.Add Name:="Size", RefersToR1C1:= _
"=IF(Sheet1!RC[-1]="""","""","" Size: "")"
End Sub

Now, your formula in Column E can look like this:

=CONCATENATE(Goal,B17,Note,C17,Size,D17)

HTH
Dana DeLouis

 

[df]

Thanks to all the help. This helped me consolidate data
for import into an Access dB and I learned a few things.

 

[hgrove]

Dana DeLouis wrote...
...

With a lot of data, it could look messy. If you would like to try something a little
different...

...

If you're going to go the programming route, why not write something
general?


Code:
--------------------

Function psc(p As String, s As String, ParamArray a() As Variant)
Dim x As Variant, y As Variant

For Each x In a

If IsArray(x) Then
For Each y In x
If y Like p Then psc = psc & s & y
Next y

Else
If x Like p Then psc = psc & s & x

End If

Next x

psc = Mid(psc, Len(s) + 1)
End Function

--------------------


which could be used in the array formula

=psc("?*:?*",",",{"Goal:","Note:","Size:"}&B1717)