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

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

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

Thank you very much Carson. Much appreciated.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.