Ideas on how to strip file extensions from a file name?

Let me explain my scenario:

  • User posts a document to SharePoint
  • Via Power Automate, Smartsheet is notified that that document has been posted in SharePoint. This notification contains the full file name from SharePoint (including .docx, .xlsx, .txt, .jpg, whatever file extension).
  • In order to associate this event to the proper row in my Smartsheet tracker, I need the file name without the file extension. My plan is to do a lookup from my tracker sheet to this 'From SharePoint' sheet.

So I need to be able to strip the file extension from the "." to the end, which can be a variable length--as shown above, that could be 4 or 5 characters. So I can't use the LEFT function. I thought about using SUBSTITUTE, however, I would have to anticipate all possible file extension possibilities...and I am not even sure if I can chain multiples together anyway.

Does anyone have any brilliant ideas to solve this? I researched this, and called Smartsheet Support, but nothing so far. Hoping someone in the Community is inspired to solve this (or has already tackled this).


Steve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!