Ability to Find & Replace text in Formulas

svenu · 06/27/23

Hi Team,

I am aware that this functionality isn't currently available. But, as someone who works on rows & rows of data every day, replacing values in a formula using the Find & Replace feature is extremely helpful and time-saving.

As I type this, I have the daunting task of going back to my sheet on SmartSheets and manually changing every single cell with a formula which will take me hours to complete. Can we please enable this feature?

Regards

Sowmya

Shonnify · 06/30/23

Agreed. I have to constantly change and replace names and dates each quarter. And also have to change dates each quarter. Eliminate the manual updates. Some of my lines are over 500 rows, each with an name assigned.

Meredith Rhodes · 10/30/23

I came here to say this. This feature would make a 3 hour manual update into a 3 minute manual update.

svenu · 11/09/23

@Meredith Rhodes Exactly!

Gina Prego · 11/09/23

This is much needed

Ed Gadd · 02/15/24

Please can a tool be created to find and replace text within formula.

I see this request has been mentioned frequently on here. I hoping release of this will be soon.

Jessica Limke · 02/19/24

This would be incredibly helpful when I make annual updates to formulas - would reduce the process from hours into minutes.

Gina Prego · 02/21/24

Control+F

AmandaWebb · 03/28/24

https://community.smartsheet.com/discussion/comment/421096#Comment_421096

unfortunately, that does not work to find and replace values within a formula. otherwise, everyone in this thread would be overjoyed, sadly as of today that still does not work.

gregthomas · 04/04/24

I agree with needing find & replace capability with formulas. I do this all the time in excel and couldn't live with out it.

Depending on what part of the formula you're wanting to find & replace, one work around may be to have a helper column that has a piece of your formula in it. @Shonnify mentioned replacing names and dates each quarter. Typically those are formula pieces that you could house in other columns and just reference them in your formula.

I.e., instead of =IF([Name Column]@row = "Frank", "win", "lose"), you would have =IF([Name Column]@row = [Formula Helper]@row, "win", "lose"). Both of these result in a win, but in one you would have to change the name in the formula and in the other you would just need to change the name in the helper column with a find & replace. This can be used in many types of formulas and scenarios.

Shonnify · 04/04/24

@gregthomas Yes, the helper column works well for the dates. I found this option which has been working nicely:

=[start date]@row+90 and =[finish date]@row+90

Add date column, rename add formula, then copy paste special in original date column, and delete the new one.

Each quarter may require +90, 91, 92, 93 depending how the dates fall. I will try your option as well, thanks for the tip.

The formulas, or find and replace options do not work well with hidden, closed or indented formats. We also need an "expand all" option to help with this. Otherwise, you have to open each + separately. Sample:

Andrée Starå · 04/05/24

Hi @svenu @Shonnify @Meredith Rhodes @Gina Prego @Ed Gadd @Jessica Limke @AmandaWebb @gregthomas

I hope you're well and safe!

I wanted to mention that if you have a Control Center set up, you can use Global Updates to find and replace formulas.

Would that work/help?

I hope that helps!

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!

Kam114 · 05/08/24

Why not adding a "also look into formulas" checkbox that can be added to the find and replace dialogue window? In my time working with several apps, I have not thought of more enhancements than I have with smartsheet.

Eliana Davalos · 07/05/24

Hi @Andrée Starå,
You mentioned above, that if we "have a Control Center set up, [we] can use Global Updates to find and replace formulas." Would you please elaborate more on this? I have a formula that always changes on me whenever I move data around in cells. Because I have to do this same "movement" every month, it become very time consuming to check almost every single cell in a particular column, and having a way of searching text w/in a formula would be SUPER helpful!

Leroy Noriega · 07/05/24

We need a Smartsheet version of Ctrl + H

Sign in to submit new ideas and vote

Ability to Find & Replace text in Formulas

CI Analyst II, Niagara Bottling

Idea Submitted · Last Updated 06/27/23

Comments

CI Analyst II, Niagara Bottling

Categories