Text to columns vs IF statement
I have daily report that contains a string of text (Reasons) regarding issues that occurred overnight. These are usually anywhere from 2 to 20 in each report, and while that is manageable on a day to day basis, I'm trying to pull in the last year's worth of data and that is entirely too much to parse by hand.
I'd like to be able to copy those Reasons into a Smartsheet and have it then automatically do a "Text to Columns" function. I used the following formulas so far:
For Reason1: =LEFT(Reason@row, FIND("CW#", Reason@row) - 1)
For Ticket#: =RIGHT(Reason@row, LEN(Reason@row) - FIND("CW#", Reason@row))
- In the Reason1 field now, when the expected text to find is not there, it gives an "#INVALID VALUE" message, and puts the entire string in the Ticket# field. What needs to happen is that the string should be in Reason1 and nothing should be in Ticket#.
- In the Ticket# field, even though I selected CW# as the qualifier, it only uses C, leaving W# in the Ticket field. I would just use # but that symbol gets used in other parts of the string sometimes, so it is not a valid qualifier. Nor is just C or W for obvious reasons. Does Smartsheet only allow a single character here, or is there a way to get it to identify a set of characters? "CW#" appears to work in the formula for Reason1, because in most of the reasons, there is a C long before CW# appears, so I would assume it would split the string at the first C.
- The Reason2 field was my attempt to write another formula to correct the issue where #INVALID VALUE shows up the Reason1 field... I was thinking that if I had to, I could write something that basically says "If Reason1 is Invalid, use the test from the Reason field. So far, I wrote =IF(AbendTemp@row = "#INVALID VALUE", " "), AbendTemp@row but Nested IF statements still confound me, so I'm not surprised to find it doesn't work. I also really don't want to have yet another formula column to try to pull out this info. I'd prefer to be able to handle it in a single function if possible, combined with the current formula I'm using in the Reason1 field.
Have I confused everyone enough? And is this even feasible? Hopefully the image helps.
Help Article Resources
Check out the Formula Handbook template!