Help

Including an IF Formula into a CONCATENATE

Topic Labels: Formulas
Solved
Jump to Solution
1046 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Christina_Spach
4 - Data Explorer
4 - Data Explorer

Dear community,

I am working on a table for an art collection. The first field is supposed to sum up my most important information about each piece with a formula:

CONCATENATE({artist},", “,{title},”, “,{year},”, ",{height (cm)}, " x “, {width (cm)}, " cm” , ", ",{technique})

This works perfectly fine so far, but I am struggeling to include an IF-Formula within the existing formula: Since a few of the works have a depth worth mentioning, I also want to include the depth of the pieces in the first field, but only IF the field {depth (cm)} is NOT EMPTY. As far as i researched this would be IF (depth != BLANK (), " x ", {depth}) , but I didn’t manage to make this work, let alone yet including it into the existing one.

Just to sum it up, I want the formula to be displayed like this, with the bold part only being shown if the field is not empty:
artist, title, year, height x width x depth cm, technique

I’m sorry if this is obvious but I’m very new to airtable.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Christina_Spachtholz! :grinning_face_with_big_eyes: There are a couple issues with the IF() function that you’re trying to add:

First, Airtable functions cannot have a space between the function name and the opening parenthesis; e.g. IF(...), not IF (...)

Second, I’m guessing you want to combine the " x " and the value of the {depth} field. To do this, use the & operator between them, which is a shortcut concatenate operator. All functions separate their arguments with commas, but each function does something unique with the arguments that are passed. The CONCATENATE() function combines all the pieces passed into a single string, but the IF() function structure is different:

IF(CONDITION, VALUE_IF_TRUE, OPTIONAL_VALUE_IF_FALSE)

With the IF() function you tried, it will output only the " x " portion if the {depth} field is blank, and only the {depth} field itself if it’s not. The correct structure for just that portion would be:

IF({depth (cm)} != BLANK(), " x " & {depth (cm)})

Here’s the full formula modified with these items fixed:

CONCATENATE({artist},", ",{title},", ",{year},", ",{height (cm)}, " x ", {width (cm)}, IF({depth (cm)} != BLANK(), " x " & {depth (cm)}), " cm" , ", ",{technique})

Screen Shot 2020-08-08 at 6.54.55 AM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Christina_Spachtholz! :grinning_face_with_big_eyes: There are a couple issues with the IF() function that you’re trying to add:

First, Airtable functions cannot have a space between the function name and the opening parenthesis; e.g. IF(...), not IF (...)

Second, I’m guessing you want to combine the " x " and the value of the {depth} field. To do this, use the & operator between them, which is a shortcut concatenate operator. All functions separate their arguments with commas, but each function does something unique with the arguments that are passed. The CONCATENATE() function combines all the pieces passed into a single string, but the IF() function structure is different:

IF(CONDITION, VALUE_IF_TRUE, OPTIONAL_VALUE_IF_FALSE)

With the IF() function you tried, it will output only the " x " portion if the {depth} field is blank, and only the {depth} field itself if it’s not. The correct structure for just that portion would be:

IF({depth (cm)} != BLANK(), " x " & {depth (cm)})

Here’s the full formula modified with these items fixed:

CONCATENATE({artist},", ",{title},", ",{year},", ",{height (cm)}, " x ", {width (cm)}, IF({depth (cm)} != BLANK(), " x " & {depth (cm)}), " cm" , ", ",{technique})

Screen Shot 2020-08-08 at 6.54.55 AM

Thank you so much! This worked like a charm! :star_struck:

And yes, this was exactly the issue i had, it only displayed either the " x " or the {depth} (I originally didn’t have a space after the IF() function, this only just happened by accident while retyping)! I had no idea the structure was so different - thanks for the explanation! :grinning_face_with_smiling_eyes: