Concatenate formula with "0" value

[Sally_Wuu]

Hi!
A while ago I asked for help to concatenate several values together (kids’ ages) and the following was suggested by Justin_Barrett, which I am using now, thank you Justin.

SUBSTITUTE(
TRIM({K1 age} & IF({K2 age}, " " & {K2 age})
& IF({K3 age}, " " & {K3 age})
& IF({K4 age}, " " & {K4 age})
& IF({K5 age}, " " & {K5 age}))
, " ", ", "
)

However, it doesn’t seem to allow for a “0” value. Can someone advise why and/or how to modify this formula to allow for the “0” value?

[Justin_Barrett · Accepted solution]

First off, you can tag users here by typing @ and then the user name. In fact, just type @ and the first few characters, and you’ll see a popup list of users appear, where you can choose the one you want, and it’ll be added to your post/comment.

The reason that 0 isn’t working is that something like IF({K2 age}, ... effectively looks for a true or false value to come back from the listed field. A blank field equates to false, and a non-empty field normally equates to true. However, the number 0 also equates to false. To truly see if the field is just blank in a case where you still want 0 to be processed, you will need to compare the field value against the function BLANK() using the != operator, which means “not equal”. While BLANK() can be avoided in lots of situations, this isn’t one of them. :slightly_smiling_face: Here’s the formula with that change applied:

SUBSTITUTE(
TRIM({K1 age} & IF({K2 age} != BLANK(), " " & {K2 age})
& IF({K3 age} != BLANK(), " " & {K3 age})
& IF({K4 age} != BLANK(), " " & {K4 age})
& IF({K5 age} != BLANK(), " " & {K5 age}))
, " ", ", "
)