# Re: Adding IF formula to a column with an existing concatenate formula

Solved
3189 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey guys!

I’m looking to add an if formula to the formula below. What I need is that if the “fixture model #”, the “bulb model #” or the “miscellaneous” fields are empty, the output of the concatenate funtion is “N/A”

CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous})

1 Solution

Accepted Solutions
18 - Pluto

If you want to use a different string when a field value is empty, use an `IF`.

Here is the general syntax:

``````IF({field name}, {field name}, "other text")
``````

I also suggest that you use the `&` operator to concatenate your text instead of the `CONCATENATE` function.

``````{Name (from Orders)} &
" - (" & Quantity & ") " &
IF({Fixture Model #}, {Fixture Model #}, "N/A") &
" / " &
IF({Bulb Model #}, {Bulb Model #}, "N/A") &
" / " &
IF({Miscellaneous}, {Miscellaneous}, "N/A")
``````

You may also want to change the symbol you use between field values, as the `/` between field and the `/` in `N/A` can get confusing.

15 Replies 15
18 - Pluto

Welcome to the Airtable community!

You can accomplish this task with a combination of the `IF`, `OR`, and `NOT` functions.
They are all documented in the Logical functions section of the formula field reference.

``````IF(
OR(
NOT({Fixture Model #}),
NOT({Bulb Model #}),
NOT({Miscellaneous})
),
"N/A",
CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous})
)
``````
18 - Pluto
``````IF(
AND({Fixture Model #}, {Bulb Model #}, {Miscellaneous}),
CONCATENATE({Name (from Orders)}," - (",Quantity,") ",{Fixture Model #}," / ",{Bulb Model #}," / ",{Miscellaneous}),
"N/A"
)
``````
18 - Pluto

@Justin_Barrett’s formula is much better than mine. His formula checks to make sure that there are values in all three fields. Mine checks if any of the fields are blank (which is how you originally phrased your conditions.) The end result of both is the same, but his is more concise.

5 - Automation Enthusiast

So I guess clarity goes a long way here, lol. These are great. but what I should have said is that I still need the concatenate output of the items that ARE there. Just in the places that there is no value, I would like to insert “N/A” instead.

18 - Pluto

If you want to use a different string when a field value is empty, use an `IF`.

Here is the general syntax:

``````IF({field name}, {field name}, "other text")
``````

I also suggest that you use the `&` operator to concatenate your text instead of the `CONCATENATE` function.

``````{Name (from Orders)} &
" - (" & Quantity & ") " &
IF({Fixture Model #}, {Fixture Model #}, "N/A") &
" / " &
IF({Bulb Model #}, {Bulb Model #}, "N/A") &
" / " &
IF({Miscellaneous}, {Miscellaneous}, "N/A")
``````

You may also want to change the symbol you use between field values, as the `/` between field and the `/` in `N/A` can get confusing.

5 - Automation Enthusiast

Awesome! Thanks Kuovonne. This works perfectly. I also changed the N/A to NO to get rid of the /. Thanks for the advice.

5 - Automation Enthusiast

So I guess I have one more question. In the formula below, I should change to the & instead of using concatenate? Also, how would I get the “-” or spaces to not show if there was no value in the field?

CONCATENATE(Manufacturer, " - “, {Product Type},” - “, {Manu. Model #},” - ",{Fixture Type}, " - “,{Fixture Material},” - “,{Finish Color},” - “,{Bulb Type},” - “,Voltage,” - “,Wattage,” - “,{Beam Spread},” - ",{Kelvin/Color} )

18 - Pluto

Since you are going to re-write the formula anyway, I recommend switching to the `&` operator instead of `CONCATENATE`. (But I wouldn’t change a working formula only for the sake of changing it.)

You can include the `-` in the `IF` formula. For example, the following formula will include the `-` and the field value if there is a field value. If the field is blank, it will show nothing at all.

``````IF({Fixture Type}, " - " & {Fixture Type})
``````

The main issue arrises if the very first field is optional. If it is you have to get more creative. But concatenating `IF` statements like this one usually is sufficient.

5 - Automation Enthusiast

Hi! I was hoping I could piggy back on this topic, as my brain has reached max. RAM and I just can’t seem to get this right.
My original formula was

``````CONCATENATE(
``````

{1st Quantity}," x “,{1st Pair},”\n",
{2nd Quantity}," x “,{2nd Pair},”\n",
{3rd Quantity}," x “,{3rd Pair},”\n",
{4th Quantity}," x ",{4th Pair})

Which works fine if there are values in the 2nd, 3rd + 4th Quantity and Pair cells. However, I only want those “x” characters to show if the cells are not empty. If they are empty, I’d rather they didn’t show!