Splitting value in one column into several

Options

Hi,

Need help please.

I have 1 column that has 2 names and can be delimited by "/" or ",". I'm trying to split into 2 separate column. Since it's a free form coming from another source, I'm struggling to get a formula that will cover all three scenarios.

The objective is to split the below column into Name 1, Name 2. Below formula works for 1 scenario but not all. Below is Owner@row value.

John Doe / Mary Doe

John Doe, Mary Doe

John Doe/Mary Doe

=IF(Owner@row <> "", IF(OR(FIND("/", Owner@row) > 0, FIND(",", Owner@row) > 0), RIGHT(Owner@row, LEN(Owner@row) - FIND("/", Owner@row)), RIGHT(Owner@row, LEN(Owner@row) - FIND(",", Owner@row), Owner@row)))

Thanks in advance

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    Name 1:

    =IF([Owner]@row <> "", IF(FIND("/", [Owner]@row) > 0, LEFT([Owner]@row, FIND("/", [Owner]@row) - 1), IF(FIND(",", [Owner]@row) > 0, LEFT([Owner]@row, FIND(",", [Owner]@row) - 1))))

    Name 2:

    =IF([Owner]@row <> "", IF(FIND("/", [Owner]@row) > 0, RIGHT([Owner]@row, LEN([Owner]@row) - FIND("/", [Owner]@row)), IF(FIND(",", [Owner]@row) > 0, RIGHT([Owner]@row, LEN([Owner]@row) - FIND(",", [Owner]@row)))))

  • Caravan
    Caravan ✭✭✭✭
    Options

    Thank you very much Carson. Much appreciated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!