Prompt Title: Find and Delete Cell Content

Created 1 year ago
Votes 0
Views 233 times
0

Prompt Detail:

You can use a formula with the REGEXMATCH function to check if any of the words from the list in column A are present in the corresponding cell in columns B onwards. Then you can use the IF function to delete the value if none of the words are present. Here's an example formula you can use:

less
=IF(SUMPRODUCT(--(REGEXMATCH(B3,"(" & JOIN("|",A3:A21) & ")")))<1,"",B3)

This formula checks if any of the words in column A are present in the cell in column B, starting from row 3. If none of the words are present, it deletes the value in the cell, otherwise it leaves the value as is. You can then copy this formula and paste it to the entire range of columns B onwards.

Alternatively, you can use a script to achieve this task. Here's an example script:

javascript
function deleteValues() { var sheet = SpreadsheetApp.getActiveSheet(); var dataRange = sheet.getDataRange(); var data = dataRange.getValues(); var words = sheet.getRange("A3:A21").getValues().flat(); for (var row = 2; row < data.length; row++) { for (var col = 1; col < data[row].length; col++) { var cellValue = data[row][col].toString(); if (!words.some(word => cellValue.toLowerCase().includes(word.toLowerCase()))) { sheet.getRange(row + 1, col + 1).setValue(""); } } } }

This script loops through all the cells in columns B onwards and checks if any of the words in column A are present in the corresponding cell. If none of the words are present, it deletes the value in the cell. You can then run this script manually by going to "Tools" > "Script Editor" and pasting the code into the editor. Then, click the "Run" button to execute the script.

avatar
Ray Celeste
Shared 5 prompts
Created 1 year ago

Leave a Comment

Related Tag Prompts

84
0
0
0
0
0
Google Sheets SMS Script
1 year ago 2023-04-03 16:53:35 ben
0
0
0
0
Adjusting Survey Prompt
10 months ago 2023-08-31 19:34:50 Tom