Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Need Formula to show a data if another data is empty

Topic Labels: Formulas
681 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Rai
4 - Data Explorer
4 - Data Explorer

Hi, 

Seeking help here.. I need a formula in the Client name cell where in it will only show the client name if either of the Client or Brand columns has a name on it. 

Meaning if the Client cell has a name the Client name formula will only show "Path Social" 
and if the Brand name has a name on it "ThingTesting" it will only show in the Client name cell

Rai_0-1703034201342.png

 



2 Replies 2
Megan_Bandy
6 - Interface Innovator
6 - Interface Innovator

Hi Rai,

The simplest version would be simply Client&Brand
You could make it more complex such as highlighting if client AND brand are filled in. 
But for what you've asked this would work - unless I've missed something?

Hey @Rai
There are a couple versions of this formula that you could implement, depending on the level of complexity you require.

Here's a walkthrough of some of the potential options, each with a varying level of complexity.

If there is not an expected scenario where both the Client and Brand fields contain data, then you can simply use a formula along the lines of what @Megan_Bandy posted.

If you want the formula to prioritize the Client field over the Brand field if both fields are populated, then you could use a formula like this:

IF(
    AND(
        {Client},
        {Brand}
    ),
    {Client},
    IF(
        {Client},
        {Client},
        IF(
            {Brand},
            {Brand}
        )
    )
)

You can swap around the fields to reverse this logic, if desired.
If you want the formula to combine both values into a formatted output, then you could use a formula like this:

TRIM(
    IF(
        {Client},
        {Client} & IF(
            {Brand},
            ' - '
        )
    )
    &
    IF(
        {Brand},
        {Brand}
    )
)

This formula will join the values with a hyphen, with an additional space on both sides of the hyphen. If the Brand field returns no data, then neither the hyphen or spaces will not appear.

Now, there are some fringe cases that you may want to account for. As an example, presume you have a client or brand record whose primary field contains a comma. Airtable will place string values of linked records containing commas inside of quotation marks. If this is something that bugs you, you may want to implement a level of character replacement to clean-up the final output a bit. Here's one way that you may write such a formula:

TRIM(
    IF(
        {Client},
        REGEX_REPLACE(
            {Client} & IF(
                {Brand},
                ' - '
            ),
            '["]',
            ''
        )
    )
    &
    IF(
        {Brand},
        REGEX_REPLACE(
            {Brand},
            '["]',
            ''
        )
    )
)