1

In Google Sheets I have two data columns, S and T.

What formula would clear the value in T if the value in S is not RUN?

Sample Data

Before

S T                                               
1 Trigger Msg Status
2
3 RUN 24/09/2023 14:44:35
4 24/09/2023 14:44:35
5
6 24/09/2023 14:44:35
7
8 RUN 24/09/2023 14:44:35
  • The values in T4 and T6 (highlighted above) do not have RUN in their adjacent column S (S4 and S5).

After (Result)

S T
1 Trigger Msg Status
2
3 RUN 24/09/2023 14:44:35
4
5
6
7
8 RUN 24/09/2023 14:44:35
  • The values were cleared from T4 and T6.

Link to spreadsheet

Screenshot: Example Spreadsheet
spreadsheet screenshot

1

1 Answer 1

0

A Jedi’s strength flows from the documentation

Delete data from cells it may not; only return empty or filled values, Google Sheets can. 1

This is the way 2


1 Yoda      2 Din Djarin

To paraphrase these great thinkers, Google Sheets functions cannot clear data from cells. They can only return values. These values can also be empty.

The "Google Sheets way" uses a formula in a separate column, such as U1, that returns a subset of the values found in column T.

Two Approaches

  1. Specific Value Formula
    This formula looks for a specific value such as RUN
  2. Any Value Formula
    This formula looks for any value that contains at least one character.

1. Specific Value Formula in U1

=ARRAYFORMULA(
  { "Your_Heading_Here";
    IF(S2:S="RUN", 
       T2:T, IFNA(#N/A)) }) 
  • Returns values from T2:T where S2:S="RUN"
  • Otherwise, returns a blank value
S T                                                U                                               
1 Trigger Msg Status Your_Heading_Here
2 RUN 24/09/2023 14:44:35 24/09/2023 14:44:35
3 24/09/2023 14:44:35

2. Any Value Formula in U1

=ARRAYFORMULA(
  { "Your_Heading_Here";
    IF(LEN(S2:S), 
       T2:T, IFNA(#N/A)) }) 
  • Returns values from T2:T where LEN(S2:S)>0
  • Otherwise, returns a blank value
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.