Parse information in a single cell into their own columns (text to column)

I have a single cell that contains the following:

Lead Company Person: [UNIQUE NAME]

Legal Tracker Name: [UNIQUE NAME]

Legal Tracker Number: [UNIQUE NUMBER]

I would like to parse the information after the colon into their own rows. In Excel I would use text to column, but that's not an option in Smartsheet

Any ideas on the function I can use to achieve this?

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=RIGHT([Legal Tracker]@row, LEN([Legal Tracker]@row) - (FIND("*", SUBSTITUTE([Legal Tracker]@row, ":", "*", 3) + 1))

All comments

Paul Newcome

This can definitely get tricky, but we should be able to work through it.

The first one would be finding the first colon and the first Legal and pulling everything in between. Lets give this a try:

=MID([Legal Tracker]@row, FIND(":", [Legal Tracker]@row) + 2, (FIND("Legal", [Legal Tracker]@row) - 1) - (FIND(":", [Legal Tracker]@row) + 2))

For the second one, we can use similar logic, but we will tell each of the FIND functions to start looking after the first "Legal":

=MID([Legal Tracker]@row, FIND(":", [Legal Tracker]@row, FIND("Legal", [Legal Tracker]@row) + 1) + 2, (FIND("Legal", [Legal Tracker]@row, FIND("Legal", [Legal Tracker]@row) + 1) - 1) - (FIND(":", [Legal Tracker]@row, FIND("Legal", [Legal Tracker]@row) + 1) + 2))

And for the third we should be able to use start with a RIGHT function and subtract the location of the third column from the total number of characters to get how many characters we should pull.

=RIGHT([Legal Tracker]@row, LEN([Legal Tracker]@row) - FIND(":", [Legal Tracker]@row, FIND(":", [Legal Tracker]@row) + 1))

Kayla Q

@Paul Newcome this is incredibly impressive!

Just one small problem in the third function: it's pulling back both the Legal Tracker Name and the Legal Tracker Number:

For the above example, Legal Tracker Number should = "n/a"

It's likely not helping you that I'm blurring out some of the info. Let me know if that is a problem.

Paul Newcome

Try this:

=RIGHT([Legal Tracker]@row, LEN([Legal Tracker]@row) - (FIND("*", SUBSTITUTE([Legal Tracker]@row, ":", "*", 3) + 1))

Kayla Q

@Paul Newcome you're some kind of genius!

Paul Newcome

Haha. Stubborn is more like it. A lot of trial and error has gone into figuring some of the things out.

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.