Skip to main content

There are 3 long-text fields: Author, Name and Date.
I want to combine the 3 fields into 1 field with delimiter |. Any field could be empty and I don’t want a trailing or duplicate delimiter hanging around.

Author & " | " & Name & " | "& Date
John Smith | A Book | 1998

When Author is empty:
| A book | 1998
leaves an unwanted | at the beginning. Similarly you might get a trailing | or double | |

How would you eliminate this issue? TRIM() only works with unwanted whitespaces and a nested IF() is messy and needs to take into account 8 possibilities.

I played around with various combinations of SUBSTITUTE(), FIND(), LEFT(), and RIGHT(), but I think your best bet is the following IF() statement:

IF(
    Author,
    IF(
        Name,
        Author&'|'&Name&
            IF(
                Date,
                '|'
                ),
        Author&
            IF(
                Date,
                '|'
                )
        ),
    IF(
        Name,
        IF(
            Date,
            Name&'|',
            Name
            ),
        ''
        )
    )&
Date

It’s not as elegant as it might be — I couldn’t come up with a pleasant way to eliminate that double IF(Date... clause — but it works correctly with all 8 possible combinations.

Edit: Any reason for using Long Text vs Single-line Text fields? The formula works with either, but gallery and kanban views will look better with Single-line Text…

I played around with various combinations of SUBSTITUTE(), FIND(), LEFT(), and RIGHT(), but I think your best bet is the following IF() statement:

IF(
    Author,
    IF(
        Name,
        Author&'|'&Name&
            IF(
                Date,
                '|'
                ),
        Author&
            IF(
                Date,
                '|'
                )
        ),
    IF(
        Name,
        IF(
            Date,
            Name&'|',
            Name
            ),
        ''
        )
    )&
Date

It’s not as elegant as it might be — I couldn’t come up with a pleasant way to eliminate that double IF(Date... clause — but it works correctly with all 8 possible combinations.

Edit: Any reason for using Long Text vs Single-line Text fields? The formula works with either, but gallery and kanban views will look better with Single-line Text…


Looks like this Is the best way as any other methods would still require more processing cycles. Thanks!

They really should be single line texts but I learned it’s better to leave a field multi line in general because my users hate it when they press enter and it doesn’t give them a new line.

Terms & ConditionsAccessibility statement