handling nulls in concatenated string

  • Thread starter Thread starter df
  • Start date Start date
D

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?
 
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)
 
Or a shorter kludgy:
=IF(B17<>"","Goal:"&B17&IF(OR(C17,D17<>""),",",""),"")&IF(C17<>"","Note:"&C1
7&IF(D17<>"",",",""),"")&IF(D17<>"","Size:"&D17,"")


Sandy
 
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
Click to expand...
",",""))-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
 
hgrove said:
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)
Click to expand...

lol
indeed I should have thought about that
thanks for this
Frank
 
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
 
Thanks to all the help. This helped me consolidate data
for import into an Access dB and I learned a few things.
 
Dana DeLouis wrote...
...
With a lot of data, it could look messy. If you would like to try something a little
different...
Click to expand...
...

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:"}&B17:D17)
 
Back
Top