Splitting value in one column into several
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

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)))))

Thank you very much Carson. Much appreciated.
Help Article Resources
Categories
Check out the Formula Handbook template!