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?
Nesting IF formulas
+3
+7Welcome @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!
+3Welcome @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?
+7Everything 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
+3This 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()
)
+7I 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!
+29While 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.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.