remove file extensions from cell

Jared Ross

I am pasting in a bunch of file names into a column (ID_Dump) and need to remove the extension ".png" from the cells. How exactly would I accomplish this?

Also, not sure if it makes a difference, but I have another column (ID Checkbox) referencing the ID number in it's row (ID) to see if it's anywhere in the ID_Dump column. If it is, it automatically checks. some of the formulas I've tried have thrown that ID Checkbox column out of whack.

Andrée Starå

Hi @Jared Ross

You can use find and replace.

CTRL + F or CMD + F.

More info

Find and Replace Values Within a Sheet | Smartsheet Learning Center

https://help.smartsheet.com/articles/2115655-find-replace

Use Find and Replace to search for specific values in a sheet and replace those values one at a time or all at once.

Would that work?

I hope that helps!

Be safe and have a fantastic day!

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!

Jared Ross

That works, but I was looking for something a bit more automated. I'm using a very large data set across many sheets, and eventually will be bringing in others to work on them. Just trying to minimize steps. Is there any formulas that will do it automatically when I paste in the file names?

Andrée Starå

@Jared Ross

Ok.

Yes, you could add a so-called helper column where you paste the date and then in the main column you'd use the SUBSTITUTE function to replace the filename.

More info.

SUBSTITUTE Function | Smartsheet Learning Center

https://help.smartsheet.com/function/substitute

©2020. All Rights Reserved Smartsheet Inc.

Would that work?