0

Edit

Entered different characters for string to void confusion with row numbers

I have a information written in some cells:

Column A                                            | Column B
Scrambled Egg with Toast x2 (Butter)                | Strawberry Smoothie x3 (Cream)
Banana Cake with Icing and Sprinkles x3 (Oven 180c) | Scrambled Egg with Toast x1 (Butter)
Strawberry Smoothie x3 (Cream)                      | Ice Cream and Waffles x3 (Freezer)
Ice Cream and Waffles x3 (Freezer)                  | Banana Cake with Icing and Sprinkles x3 (oven 180c)

I would like to some help to write a script that searches for (part) string in the columns above and replace the whole cell.

Finding: "Scrambled Egg"       | replacing entire cell with: "SE+T"
Finding: "Banana Cake"         | replacing entire cell with: "BC+IS"
Finding: "Strawberry Smoothie" | replacing entire cell with: "SS"
Finding: "Ice Cream"           | replacing entire cell with: "IC+W"

Resulting in:

Before Search and Replace

Column A                                            | Column B
Scrambled Egg with Toast x2 (Butter)                | Strawberry Smoothie x3 (Cream)
Banana Cake with Icing and Sprinkles x3 (Oven 180c) | Scrambled Egg with Toast x1 (Butter)
Strawberry Smoothie x3 (Cream)                      | Ice Cream and Waffles x3 (Freezer)
Ice Cream and Waffles x3 (Freezer)                  | Banana Cake with Icing and Sprinkles x3 (oven 180c)

After Search and Replace

Column A            | Column B
SE+T                | SS
BC+IS               | SE+T
SS                  | IC+W
IC+W                | BC+IS

.

.

I've looked around for various 'Find and Replace' solutions and ways to simplify the search and the answers found from this question seemed to make sense however one returned only the first search:

function findReplace() {
  const ui=SpreadsheetApp.getUi();
  const ss=SpreadsheetApp.getActiveSpreadsheet();
  const srchA=['Scrambled Egg','Banana Cake','Strawberry Smoothie'];
  const vA=['SE+T','BC+IS','SS'];
  const sh=ss.getActiveSheet();
  const rg=sh.getRange(2,2,sh.getLastRow()-1,sh.getLastColumn()-1);
  const v=rg.getValues();
  v.forEach(function(r,i){
    r.forEach(function(c,j){
      srchA.forEach(function(s,k){
        if(v[i][j].toString().includes(s,k)) {
          sh.getRange(i+2,j+2).setValue(vA[k]);
        }
      });
    });
  });
}

and the other didn't return any result.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var searchRange = sheet.getDataRange();
var lastColumn = searchRange.getLastColumn();
var lastRow = searchRange.getLastRow();

function findReplace() {
  var rangeValues = searchRange.getValues();
  for ( i = 0; i < lastColumn; i++){
    for ( j = 0 ; j < lastRow; j++){
      var value = rangeValues[j][i];
      if(typeof value != "string") { continue; }
      if(value.indexOf("Scrambled Egg") > -1){
        var newValue = value.replace("Scrambled Egg", "SE+T")
        sheet.getRange(j+1,i+1).setValue(newValue);
      }
      else if (value.indexOf("Banana Cake") > -1){
        var newValue = value.replace("Banana Cake", "BC+IS")
        sheet.getRange(j+1,i+1).setValue(newValue);
      }
      else if (value.indexOf("Strawberry Smoothie") > -1){
        var newValue = value.replace("Strawberry Smoothie", "SS")
        sheet.getRange(j+1,i+1).setValue(newValue);
      }; 
    };
  };
};

Sample Sheet

3
  • Why is row 4 in column B yellow, why not Blue?
    – CodeCamper
    Oct 25, 2022 at 21:12
  • Hello @CodeCamper, I apologise. There are no row labels so row 4 in Column B has the string "Four Three" so when the search is made "Four" is what would change the cell to "Yellow". Will try to use different characters to make it more readable. Thanks
    – Stephanie
    Oct 25, 2022 at 21:25
  • @CodeCamper, Hopefully the edits made makes this a little clearer. Please also take a look at the Sample Sheet
    – Stephanie
    Oct 25, 2022 at 22:12

1 Answer 1

1

You can use the following function to do the operation you are looking for:

=arrayformula(concatenate(map($D$1:$D$4,lambda(fox,If(iserror(find(fox,A3)),"",if(find(fox,A3) ,indirect("E"&row(fox)),0))))))

Replace d1:d4 with the first column of your before text and make sure the second column is the replace text, replace a3 with the cell you want to replace.

5
  • thanks for taking the time to answer. However, have tried to follow your suggestion as best and have received no result. Is it also possible to use script?
    – Stephanie
    Oct 26, 2022 at 15:37
  • @SCTJ you need to make a table with the before and after changes ‘d1’ needs to say ‘Scrambled Egg’ and ‘e1’ needs to say ‘SE+T’ ‘a3’ needs to say ‘Scrambled Egg with Toast x2 (Butter)’
    – CodeCamper
    Oct 26, 2022 at 16:01
  • @SCTJ if that doesn’t help please show me the sheet where you are trying to use the formula
    – CodeCamper
    Oct 26, 2022 at 16:07
  • Thanks @CodeCamper. Have adjusted your instructions though it doesn't return all results. Sheet as requested (also found at end of question, just in case.) docs.google.com/spreadsheets/d/…
    – Stephanie
    Oct 26, 2022 at 18:06
  • @SCTJ just drag the formula down
    – CodeCamper
    Oct 26, 2022 at 19:16

Your Answer

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

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