扫描一列中的单元格以获取特定的文本值，然后在另一列的同一行中输入不同的文本值 [英] Scan cells in one column for specific text value, then input different text value in same row in another column

问题描述

我们有一个电子表格正在使用，工作人员在其中一列中输入文本值，这些文本值内是我们要寻找的某些单词.

We have a spreadsheet we are using where personnel input text values in one column, within these text values are certain words we want to hunt for.

如果它们出现，我们想用另一个词填充另一列.例如:

If they appear, we want to populate another column with another word. E.g.:

Col1: 'today i went to the beach'
function finds 'beach'
function enters 'sand' in same row of Col5.

任何帮助都将不胜感激.

Any help greatly appreciated.

推荐答案

这是一种简单的方法.它不处理包含多个术语的单元格，也不处理包含在另一个单词中的术语.例如.如果术语为see，则seething也将匹配.

Here's a simple approach. It doesn't deal with a cell containing multiple terms or with a term being embedded in another word. eg. If a term were see then seething would also match.

function findTerms() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data  = sheet.getDataRange().getValues();

  // Key-value pairs of a term to look up and a value to display
  var pairs = [
    ["from", "to"],
    ["this", "that"],
    ["...", "..."]
  ];

  var inspectCol = 1; // The column you want to look in
  var resultsCol = 2; // The column to write results to  

  // loop over the rows of data
  for(var i=0; i < data.length; i++){

    // loop over the terms and values
    for(var n in pairs){
      var term = pairs[n][0];
      var val  = pairs[n][1];

      // if the specified column in the current row contains the current term
      if(data[i][inspectCol-1] && data[i][inspectCol-1].indexOf(term) != -1){

        // write val to the results column
        sheet.getRange(i+1, resultsCol).setValue(val);
      }
    }
  }
}

要想变得更聪明并做一些事情，例如只查找完整的单词，请使用match()和正则表达式，而不要使用indexOf().

To get fancier and do things like only find complete words, use match() and regular expressions, instead of indexOf().

这篇关于扫描一列中的单元格以获取特定的文本值，然后在另一列的同一行中输入不同的文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！