If cell contains a text then add text automaticall

问题:

I have sheet that has two columns, the first one contains "Done", "Escalate", "reject". Now what i want is When i type escalate it will generate "unable to verify online" on the other column..

| A      | B                      |                                                    
|Done    |                        |                  
|Escalate|Unable to verify online |              
|Reject  |                        |                                                

i'd try =if(A2 = "Escalate","HAHA","") in column B line 2 and it works, now what I want is to apply this to whole column.

for example, if the word escalate is found anywhere in column A then the unable to verify will appear on column B on the same row..

Any idea?

回答1:

You would have to write a custom function to do that. From your Spreadsheet, follow Tools-->Script Editor... on the menu bar. Enter the following code at the end:

function fillColB() {
  var s = SpreadsheetApp.getActiveSheet();
  var data = s.getDataRange().getValues();
  var data_len = data.length;
  for(var i=0; i<data_len; i++) {
    if(data[i][0] == "Escalate") {
      s.getRange(i+1,2).setValue("unable to verify online");
      }
    }
  }

There are several ways to call the function, simplest would be to enter the formuala =fillColB() in a cell and click on Enter. Alternatively, you could set up an event or add a menu item.

回答2:

You can do this just with Google Sheet's Functions. If you are trying to configure it based on a specific string, you can use: =IF(E5="Y",1, ) where E5 is the cell with text, "y" is the desired text in the column", 1 is the result you want to output upon the desired text, and " " is the desired output if the text is not correct.

If you instead want to figure out if there is any text you can use IsBlank(E5) instead of the E5="y". Ex =IF(ISBLANK(E16), ,1)

Google also has ISTEXT or ISNUM if you want to get more specific.