Control Center Global Updates: Find / Replace

Find/Replace data in your Control Center portfolio

1. Navigate to your selected program in Control Center, select Manage Program > Global Updates > Find/Replace.
2. Name your update.
3. Select the blueprint and template to update.
4. Enter your Find and Replace with values.
5. Use the In these columns dropdown to select the columns to update.

Select Advanced Options to add additional conditions to your update, or select Add Find/Replace to run more than one Find/Replace operation in a single Global Update.

6. Select Create & Run to test your changes. 

Brandfolder Image

7. Complete the Global Update to apply your changes. 

Use Find/Replace in the profile data

To run a Find/Replace update for profile data fields:

1. Select Advanced Options > Add Condition > Select test dropdown > [Row]
2. In the Select condition dropdown > is Profile Data

Use Find/Replace in the Summary section of your sheet

By default, Find/Replace doesn't change rows in the Summary section of a sheet (where all the profile data is stored).

To make changes to profile data values or formulas in the Summary section, use the Advanced Options section to add the condition [Row] is Metadata.

When you do this, Find/Replace applies only to the Summary section and skips the rest of the sheet.

Changing the name of a piece of profile data doesn't automatically update the configuration. You need to update changes in profile data names using the Control Center builder. 

Restrictions with Find/Replace

Some column types are unavailable to use Find/Replace or have restrictions. The following table provides further guidance.

Restrictions on Contact List column data

The value in the Find box must be the full name of the person displayed on the sheet or their email address. That means you can use Joe Smith or joe.smith@mbfcorp.com.

The values in the Contact List columns must match exactly. For the Find value, go to Advanced Options > Restrict to box and use one of the following:

• is equal to
• is not equal to
• is blank
• is not blank

For the Replace with value, go to Advanced Options > Restrict to box > Replace entire cell. If you use any other values for Restrict to, the update skips the Contact List columns but still processes other column types.

Restrictions on Date column data

Cells in Date columns only match the Find value when you've specified one of the following restrictions:

• is equal to
• is not equal to
• is blank
• is not blank

If you specify any other restriction, the update skips the Date columns. Similarly, the Replace restriction must use replace entire cell or you'll skip the Date columns.

For example, if you use a Find value of 01/30 and Replace with value of 02/01 while Restrict to is contains, this does NOT match a cell in a Date column that is set to 01/30/2017 because you're using the contains restriction. If you include these values in the update, the operation runs for Text/Number column types.

When you enter a date in the Find or Replace box and select a Date (or Date/Time) column, you receive a warning indicating how the date gets interpreted (for example, February 15, 2018). You then have the option to convert the value to a universal date format (YYYY-MM-DD).

Converting to a universal date format is a good best practice for matching Date columns as it helps avoid confusion caused by different date display formats (for example, mm/dd/yy or dd/mm/yy).

Smartsheet displays dates according to each user’s personal settings.

Additional ways to use Find/Replace

Use Control Center to perform a partial Find/Replace

Select Advanced Options to display a Restrict to option under the Find and Replace boxes.

You can indicate more advanced rules to set how the Find value matches to cells:

• Matches the entire cell: is equal to (default), is not equal to
• Matches a part of the cell: contains, does not contain, begins with, ends with
• Compares the entire cell: is greater than, is less than
• Other aspects of the cell: is blank, is not blank, is a number

Brandfolder Image

You can specify how the Replace value updates the cell:

• Replace entire cell (default)
• Replace matching text

Control whether Find matches uppercase and lowercase letters

By default, the Find value ignores the case. Select the match case option in the Restrict to box to ensure that matching is case sensitive.

Update formulas using Find/Replace

If the Find value begins with an equal sign (=), it matches the formula in a cell instead of the displayed result. Similarly, if the Replace value begins with an equal sign (=), it updates the entire cell with that formula. You can only Find/Replace entire formulas.

You may update a value to a formula, a formula to a value, or a formula to a revised formula.

Limitations on where to use formulas

You receive an error message if you attempt to place a formula in any of the following column types:

• Contact List
• Any column designated as Duration, % Complete, or Start Date in Project Settings

You can put a formula in the following column types, but that may produce unexpected results:

• Checkbox/Flag/Star - the result of the formula must be true/false
• Date columns - the result of the formula must be a date

Advanced conditions with Find/Replace

You can set conditions that must be satisfied on other columns in a row besides the Find value before the replacement occurs.

For example, you want to update the duration of the Identify key risks task from 4d to 6d, but you don't want to update other tasks that have the same duration. 

Use the following specification:

Find string = 4d, Replace string = 6d and specify an advanced condition that Task Name is equal to Identify key risks.

You can specify multiple advanced column conditions, and you can choose whether all or any of the conditions must be met.

Multi-select columns such as multi-contact or multi-dropdown support Find/Replace.

Using the [Row] condition

With the [Row] condition, you can specify properties of the row that aren't values. You can only specify one [Row] condition at a time.

The following table lists some recommended uses for [Row].