将查找和替换限制为整个单词谷歌表.gs函数 [英] Limit Find and Replace to whole words google sheets .gs function

问题描述

我有一个查找和替换功能，它将字符串中的aa的every实例替换为字符串，在VBA中为xlPart

I have a find and replace function, it replaces every instance of aa in a string, in VBA it would be xlPart

是否可以设置功能，以便它替换only整个单词或使用VBA语言xlWhole

Is there a way to set the function so that it replaces only whole words or in VBA language xlWhole

谢谢

function FindReplaceWSHT(){

    replaceInSheet("Elements",'aa','ZZZ');
}

function replaceInSheet(shtName, to_replace, replace_with) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shtName);

//get the current data range values as an array
var values = sheet.getDataRange().getValues();

//loop over the rows in the array
for(var row in values){

//use Array.map to execute a replace call on each of the cells in the row.
var replaced_values = values[row].map(function(original_value){
  return original_value.toString().replace(to_replace,replace_with);
});

//replace the original row values with the replaced values
values[row] = replaced_values;
}

//write the updated values to the sheet
sheet.getDataRange().setValues(values);
}

推荐答案

Google Apps脚本基于JavaScript，因此您可以使用正则表达式单词定界符\b.

Google Apps Script is based on JavaScript, so you could use the regular expression word delimiter \b.

示例:

function test(){
  var sheetName = 'Elements';
  var to_replace = 'aa';
  var replace_with = 'ZZZZ';
  replaceInSheet(sheetName,to_replace,replace_with);

}

function replaceInSheet(sheetName,to_replace,replace_with){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();
  var output = [];
  for(var row in values){
    output.push([]);
    for(var col in values[row]){
      var value = values[row][col]
         .replace(new RegExp('\\b'+to_replace+'\\b','g'),replace_with);
      output[row].push(value);
    }
  }
  range.setValues(output);
}

测试

输入

  |      A
--+----------
1 |aa
2 |bb aa ccZ
3 |aabbccZ
4 |bbaacc aa
5 |aaaaaaa

输出

  |      A
--+-------------
1 |ZZZZ
2 |bb ZZZZ ccZ
3 |aabbccZ
4 |bbaacc ZZZZ
5 |aaaaaaa

这篇关于将查找和替换限制为整个单词谷歌表.gs函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！