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?
Best Answer
-
Try this:
=RIGHT([Legal Tracker]@row, LEN([Legal Tracker]@row) - (FIND("*", SUBSTITUTE([Legal Tracker]@row, ":", "*", 3) + 1))
Answers
-
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))
-
@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.
-
Try this:
=RIGHT([Legal Tracker]@row, LEN([Legal Tracker]@row) - (FIND("*", SUBSTITUTE([Legal Tracker]@row, ":", "*", 3) + 1))
-
@Paul Newcome you're some kind of genius!
-
Haha. Stubborn is more like it. A lot of trial and error has gone into figuring some of the things out.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 434 Global Discussions
- 153 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…
- Hello, I am trying to create a report or calendar sheet with these weekly rotating roles: Weekly 1st shift Call Center (Mon-Sun) Weekly 2nd shift Call Center (Mon-Sun) Weekend On-Call 1 (Sat-Sun) Week…