If cell contains a text then add text automatically to another column cell

Question

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?

raul · Accepted Answer · 2014-07-10 11:06:11Z

2

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.

edited Jul 10, 2014 at 11:06

answered Jul 10, 2014 at 10:15

raul

1,2799 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nixxhalle Over a year ago

i follow the instructions you gave but when i put the =fillColB() in a cell, it says "You dont have a permission to call set Value(Line 7)".. What does it mean?

raul Over a year ago

Try running fillColB() from the script editor before using the formula. You can do so by using the run button on the menu bar.

Atm · Accepted Answer · 2018-02-23 07:42:54Z

0

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.

answered Feb 23, 2018 at 7:42

Atm

1

1 Comment

samthebrand Over a year ago

This doesn't answer the question - it finds matching text but does not append.

Collectives™ on Stack Overflow

If cell contains a text then add text automatically to another column cell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related