Contains function - leave unchanged unless condition is met

bkw1962

I need to update cell A in each row of a very large sheet only if a certain substring exists in cell B in the same row. If the substring does not exist in cell B, leave the existing value in place, do NOT blank it out. I cannot use any alternative "else" assignment approach because these cells are already populated. In practical terms, I need to update the assignment of 100s of specific tasks to a NEW (the same) person without having all the other task assignments becoming blank.

Carson Penticuff

When you say you need to update "100s of specific tasks to a NEW (the same) person", do you mean that all cells that are to be overwritten will be overwritten with the same value?

Is this a one time thing, or is this something that will stay with your sheet going forward?

I would start by adding a new column(checkbox) to identify rows that need changed and setting a column formula to fill it:

=IF(CONTAINS("substring", [Cell_B]@row), 1, 0)

You can then set an automation to change the values in Cell A of the relevant rows. If you only need to run this once, you can set it to run on a specific date (set a future date) and then simply run it manually or deactivate/delete it after use.

This only works if all cells that meet the criteria need to be overwritten to the same value. I hope I understood your question correctly.