Combine multiple fields based on certain conditions

Topic Labels: Formulas
Solved
519 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi,

I have a formula that almost works. I'm trying to make my primary field a formula where it looks at "Column A" and looks at "Column B". If a there's a value in "Column A", enter that value as the primary field. If there's a value in "Column B", enter that value in the primary filed. If there's a value in both "Column A" and "Column B", combine those two values and place a "/" between the two fields. The formula I have looks like this:

IF(AND({Column A} != BLANK(), {Column B} != BLANK()), CONCATENATE({Column A}, "/", {Column B}),IF(Column A != BLANK(), {Column A}, {Column B}))

This does work. However this formula is adding the "/" character to the beginning of "Column A" and at the end of "Column B" when there's only one value in one of the fields.
The results look something like this:
If there's a value in "Column A" but not in "Column B":
12345/
75326/
23457/

If there's a value in "Column B" but not in "Column A":
/93664
/30483
/54214

I tried replacing

IF(Column A != BLANK(), {Column A}, {Column B}))
with this
IF({Column A} != BLANK(), LEFT({Column A},LEN({Column A})-1), RIGHT({Column B},LEN({Column B}-1)))

But that didn't remove the "/" mark in instances where only one value was in either "Column A" or "Column B"
1 Solution

Accepted Solutions
12 - Earth

Hi,
Gladly, Airtable formula flexibility allows to avoid over-engineering

``````{Column A} & IF({Column B},
IF({Column A},'/')&{Column B})``````

6 Replies 6
8 - Airtable Astronomer

It looks like you're trying to create a formula that combines values from two columns, but you're encountering issues with extraneous slashes when only one column has a value. To resolve this, you need to ensure that the slash is only added when both columns have values. Here’s how you can adjust your formula:

IF(
AND({Column A} != BLANK(), {Column B} != BLANK()),
CONCATENATE({Column A}, "/", {Column B}),
IF(
{Column A} != BLANK(),
{Column A},
{Column B}
)
)

In this formula:

AND({Column A} != BLANK(), {Column B} != BLANK()) checks if both columns have values. If true, it concatenates them with a "/" between.
IF({Column A} != BLANK(), {Column A}, {Column B}) checks if only one column has a value and returns that value. It ensures that if only one of the columns has data, no extra slash is added.
Ensure that:

Column Names: Use curly braces {} around your column names correctly.
Blank Checking: Use != BLANK() to check for empty values.

This formula should correctly handle cases where:

Both columns have values: Combine with a "/".
Only "Column A" has a value: Return that value without an extra "/".
Only "Column B" has a value: Return that value without an extra "/".

Try using this adjusted formula and see if it resolves the issue with unwanted slashes.

18 - Pluto

Try this:

``````IF(
AND(
{Column A},
{Column B}
),
{Column A} & "/" & {Column B},
IF(
{Column A},
{Column A},
{Column B}
)
)``````

12 - Earth

Hi,
Gladly, Airtable formula flexibility allows to avoid over-engineering

``````{Column A} & IF({Column B},
IF({Column A},'/')&{Column B})``````

5 - Automation Enthusiast

Thank you Saravanan for the response. The above formula still left the "/" mark character if only Column A or Column B had a value. Others in this thread provided a solution to get rid of the "/" mark when Column A or Column B had a value.

5 - Automation Enthusiast

Thank you The TimeSavingCo. This formula works like a charm!

5 - Automation Enthusiast

Thank you Alexey_Gusev. This formula works great! I appreciate how concise the formula is.