Skip to main content

A Delimiter Dilemma - combining fields with delimiter properly

  • February 26, 2018
  • 2 replies
  • 13 views
Y
Forum|alt.badge.img+14
  • Y_K
  • Known Participant

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.

2 replies

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • February 27, 2018

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…

    Y
    Forum|alt.badge.img+14
    • Y_K
    • Author
    • Known Participant
    • February 28, 2018

    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