What is the longest formula you've created?

SteyJ

As the title suggests, I am very curious to see how far some of you all have pushed the limits of Smartsheet formulas. I think it will be interesting to see everyone's longest formula, and what they created this formula for.

I'll go first: A person on the Smartsheet community was requesting a column to have employees names formatted a certain way. They wanted the first letter after a hyphenated last name to be capitalized. Essentially, converting jacob stey-stey to Jacob Stey-Stey

Here's what I created for them (which just so happens to be my longest formula)

=IF(CONTAINS("-", [Submitter User Name]@row) = 1, UPPER(LEFT([Submitter User Name]@row, 1)) + 
MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row, 1) + 1, 1)) + 
IFERROR(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 2, 
IF((FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2) >= 0, 
FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2)), "") + 
IFERROR(UPPER(MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row, 1), 2)), "") + 
MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row) + 2, 
LEN([Submitter User Name]@row) - FIND("-", [Submitter User Name]@row)), 
UPPER(LEFT([Submitter User Name]@row, 1)) + MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 1, 1)) + MID([Submitter User Name]@row, 
FIND("_", [Submitter User Name]@row) + 2, LEN([Submitter User Name]@row) - FIND("_", [Submitter User Name]@row)))

Andrée Starå

Hi @SteyJ

I hope you're well and safe!

Interesting!

A couple of years back, I was working with a client on a solution for measuring time, and I remember hitting the limit, so I had to shorten all the column names to make it work, and there weren't many characters left.

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SteyJ

https://community.smartsheet.com/discussion/comment/425095#Comment_425095

HAHAHA that's awesome!

this is only 1200 characters, so I imagine your formula was almost 3x as big!!!!

KPH

My longest formula was actually one of the easiest and only uses one function (IF - many times). But it is an interesting (to me at least) use case. 

We have a master sheet that holds lots of project details and we use these in reports, dashboards, etc. We also produce a client-facing email that contains some details from this sheet. It is a pretty HTML email. What details are included and where they appear in the email depends on the project type. The email used to be created by manually copying and pasting more than 100 individual text strings into the HTML template and then adding hyperlinks. 

My formula is essentially chunks of HTML and IF statements. On each row in the sheet, IF certain things are true, it outputs certain bits of HTML, and within those chunks of HTML, it inserts strings of text from the row. If other things are true some of the strings it outputs become hyperlinks using URLs found in other cells within the row.

I then have a simple report with a filter that just gives me this column of HTML for the rows I need for the next email. We simply copy the entire column, paste it into Notepad with some header and footer HTML, and the email is done. One copy and paste instead of hundreds. And no accidents! 

Surely not what Smartsheet is for, but I kind of love it.