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

SteveReed@WK · 07/24/20

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

Paul Newcome · 07/24/20

What is the possibility of a "." being elsewhere in the file name or is the only place the "." will be is immediately before the extension?

Andrée Starå · 07/24/20

Hi @SteveReed@WK

You could do something similar to this, as Paul described in another post.

https://community.smartsheet.com/discussion/comment/257473#Comment_257473

Example from the post. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(Email@row, FIND("@", Email@row) - 1), "_", " "), "0", ""), 1, ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", "")

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

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

Paul Newcome · 07/24/20

I was hoping for a response of the "." always being right before the extension which would give us the ability to use...

=LEFT([Full File Name Column]@row, FIND(".", [Full File Name Column]@row) - 1)

The SUBSTITUTE chain will work as long as you can think of every possible file extension type and it doesn't go beyond 4,000 characters including spaces.

SteveReed@WK · 07/24/20

https://community.smartsheet.com/discussion/comment/257613#Comment_257613

Thanks for the reply Paul. Unfortunately, right now the plan is that the file names would contain at least one other "." The items on the tracker sheet are like this A7,1, A7.2, A7.3, and A7.4 to correspond to a required document for each of Q1, Q2, Q3, and Q4. If I replace the "." with something like a "-" in the tracker sheet, do you have an clean solution for me? Since posting this question, I have continued working on this, and come up with the following cumbersome formula (which requires me to build in all possible file extensions):

=IF(CONTAINS(".docx", Request@row), SUBSTITUTE(Request@row, ".docx", ""), IF(CONTAINS(".xlsx", Request@row), SUBSTITUTE(Request@row, ".xlsx", ""), IF(CONTAINS(".doc", Request@row), SUBSTITUTE(Request@row, ".doc", ""), IF(CONTAINS(".xls", Request@row), SUBSTITUTE(Request@row, ".xls", ""), IF(CONTAINS(".png", Request@row), SUBSTITUTE(Request@row, ".png", ""), IF(CONTAINS(".jpg", Request@row), SUBSTITUTE(Request@row, ".jpg", ""), "Not Valid Request"))))))

This will at least solve my immediate issue (I need to demo this solution to a CTO), but I would love something cleaner and easier to maintain going forward.

Steve

Paul Newcome · 07/24/20

If you are going the SUBSTITUTE route then you could use my solution that Andree posted above where it is just a chain of SUBSTITUTE functions. This will cut out the need for the IF and CONTAINS functions and should shorten things up considerably.

Paul Newcome · 07/24/20

Are all file names just

letter number "." number

?

Will they only have one additional "." within the file name itself?

SteveReed@WK · 07/24/20

https://community.smartsheet.com/discussion/comment/257616#Comment_257616

Paul - YOU ARE THE MAN!

I actually just tried your LEFT solution after changing the . to -, and it works beautifully!!! ? This is so much better than having to call out all possible file types. Thanks so much! Gotta love the Smartsheet Community!

Steve

Paul Newcome · 07/24/20

Happy to help. 👍️

Geoff Rothman · 07/30/20

Another fun way to handle the variety of the file extensions AND to keep hour "period" character is to cheat a little, knowing that the extensions will never be longer than X # of characters. In my example, I'll assume 5 char suffix max.

Essentially you'll use the LEN() -5 as the "starting point" value for the optional parameter in the FIND() function to get the starting point for your LEFT() function.

So even if there are multiple periods in the file name it wont matter because you don't start searching for a "." character until the (length of string minus 5 chars) point.

Paul Newcome · 07/31/20

@Geoff Rothman Can you provide an example of your solution?

Paul Newcome · 07/31/20

@lewis hamilton Your solution will not work in Smartsheet.

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

Answers

Help Article Resources

Categories