Help

Re: Nesting IF formulas

779 0
cancel
Showing results for 
Search instead for 
Did you mean: 
CPMSM
4 - Data Explorer
4 - Data Explorer

I have 5 free form data fields, none of them are required. I need to display only the ones that receive data, and exclude the blank ones. How would I create that formula?

6 Replies 6
Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

Welcome @CPMSM

This can be accomplished by the following If statement

IF(
  {Field 1}, {Field 1}, BLANK()
 )
&
IF(
  {Field 2}, {Field 2}, BLANK()
 )
&
IF(
  {Field 3}, {Field 3}, BLANK()
 )
&
IF(
  {Field 4}, {Field 4}, BLANK()
 )
&
IF(
  {Field 5}, {Field 5}, BLANK()
 )

Hope that helps!

Thank you!  Do you what the limit is on how many fields you can list like this before it breaks?  

Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

Everything I have ever read looks like it would be difficult to hit a max

https://community.airtable.com/t5/base-design/maximum-size-of-the-formula-length-limit/td-p/116378

 

CPMSM
4 - Data Explorer
4 - Data Explorer

This is so helpful, Brian!  One more question (possibly).  

I need to add a break ('\n\n') when two or more fields are returned.  I can't seem to figure out where to put it without returning an error:

IF(
  {Italian (1)}, {Italian (1)}, BLANK()
 )
&
IF(
  {Italian (2)}, {Italian (2)}, BLANK()
 )
&
IF(
  {Italian (3)}, {Italian (3)}, BLANK()
 )

 

Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

I would add the break here:

IF(
  {Italian (1)}, {Italian (1)}&'\n', BLANK()
 )
&
IF(
  {Italian (2)}, {Italian (2)}&'\n', BLANK()
 )
&
IF(
  {Italian (3)}, {Italian (3)}&'\n', BLANK()
 )

In this manner the break will only become part of the field if there was data in the field.  If you are looking at this through a grid view you may need to increase your row height to see the break

All the best!

kuovonne
18 - Pluto
18 - Pluto

While Brian's formula works well, I would like to suggest a slightly different version. There usually are multiple ways to do things in code. Pick the way that makes the most sense to you and is the easiest for you to maintain.

 

TRIM(CONCATENATE(
  IF({field1}, {field1} & "\n\n"),
  IF({field2}, {field2} & "\n\n"),
  IF({field3}, {field3} & "\n\n"),
  IF({field4}, {field4} & "\n\n"),
  IF({field5}, {field5} & "\n\n")
))

 

Here is a detailed explanation of this formula and how it works in my doc Kuovonne's Guide to Airtable.

Note that the version in this post includes two newline characters as you indicated you wanted, but the version in my guide has only a single newline character.