Ability to Find & Replace text in Formulas

svenu

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

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

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

svenu

@Meredith Rhodes Exactly!

Gina Prego

This is much needed

Ed Gadd

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

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

Gina Prego

Control+F

AmandaWebb

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

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

@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å

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!