Combining a Nested IF Formula and Searching for the First 4 Numbers in a String


Hello Smartsheet Community,

I am trying to build a Nested IF formula to assign a site name depending on the first 4 numbers in the screening participant ID. So for example, I would want the formula to identify "S401" from the string "S401001" in the Screening ID Column and assign the name of "01/New York" in the Site column. I would want to build out the nested if to include logic for all 13 sites (i.e "S401", S402", "S403", etc.). I understand how to build the Nested IF formula, but unsure how to incorporate the searching capability.

Any help would be greatly appreciated. Thank you!


  • Alexander Ford

    Hi Aspen,

    Do you always want to use the first 4 characters from the Screening ID Column? If so you could use something like LEFT([Screening ID]@row, 4) to grab that portion of the Screening ID string for the Site column. Let us know if you think that would work!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!