Blank & Not(Blank) Formulas for Contact Information

[Karim_Badra]

Hi,

I have landline and mobile numbers in my data base. If one of them is missing, i want to show the other, if both are missing, i want to keep it blank, and if both are full i want to concantenate them. This is my formula syntax but its not working. Can you please help correct it?

IF(
AND(
{Mobile Phone 1} = BLANK(),
Landline = BLANK()
),
“Both Blank”,

IF(
AND(
{Mobile Phone 1} = BLANK(),
Landline = NOT(BLANK())
),
“Landline”,

IF(
AND(
{Mobile Phone 1} = NOT(BLANK()),
Landline = BLANK()
),
“Mobile”,
“Both”
)
)
)

[Justin_Barrett · Accepted solution]

I find it easier to look for filled fields vs empty ones, which means you can avoid using BLANK() completely. Not only that, but this can be greatly simplified by reordering the logic.

Here’s the key: virtually any non-empty field (that’s not a number field) is treated as “truthy”—i.e. equivalent to True—and any empty field is considered “falsy”. You can test for this by only including the field name, like so:

IF({Field Name}, result_if_true, optional_result_if_false)

The same goes for comparing multiple fields using AND() and OR().

With that in mind, I’d start this formula by looking to see if both fields are full, and combining them if they are:

IF(
  AND(
    {Mobile Phone 1}, Landline
  ), {Mobile Phone 1} & " / " & Landline
)

If that AND() returns False, we know that we either have one of the two numbers, or no number at all. In any of those three situations, we can simply concatenate both of those fields, and we’ll either have one number or no number.

With that, the full formula is this:

IF(
  AND(
    {Mobile Phone 1}, Landline
  ), {Mobile Phone 1} & " / " & Landline,
  {Mobile Phone 1} & Landline
)

[Karim_Badra]

Brilliant Justin! Thank you!!