Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 22, 2020 08:13 AM
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?
Solved! Go to Solution.
Jul 25, 2020 09:25 AM
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}))
, " ", ", "
)
Jul 25, 2020 09:25 AM
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}))
, " ", ", "
)