Combine multiple fields based on certain conditions

[Danny_Mohill]

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"

[Saravanan_009]

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.

[TheTimeSavingCo]

Try this:

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

 

[Alexey_Gusev · Accepted solution]

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

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

[Danny_Mohill]

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.

[Danny_Mohill]

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

[Danny_Mohill]

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