What is the best method (formula or javascript) for looking at a range of cells (specifically, an entire column), checking to see if there is a text string within each cell that meats certain criteria, and then replacing all of the text within each cell with my specific text.
I need to replace text from another source with my text. An example would be if the text “BLUE” is present in the cell’s text string (i.e. “JSA BLUE WALL JSIA”), then replace the entire cell text with “GAME BLUE REGION”. I have 5 text conditions I am trying to find, match, and then replace with 5 other text values. I have tried using the IF/IFS/FIND formulas but I continue to get errors with those.
—–
Kevin
Use a formula to do this. If the column in question here is column B, then insert a column C to use for the purpose.
In column C, use an IF function to check for the existence of the word in the text. If it is NOT there, then output the original value. If it is there, then output your replacement for the entire value.
To determine if the value is there, use FIND. This gives an error if it isn't found, so surround it in ISERROR.
So here would be a function to do this:
IF(ISERROR(FIND("BLUE",B2)),B2,"GAME BLUE REGION")
If you want it to be case insensitive, then look at the SEARCH function instead of FIND.
When you have this formula throughout column C, then your column C is now your results. You can use it as-is, leaving the originals in B. Or, you can copy the complete contents of column C and Edit, Paste Formulas Results to replace the formula with the results (making the results permanent and not dependent on B). Then you can delete column B.
I am able to get your suggested formula to work for the single argument of "BLUE." However, I am trying to find a total of 5 text conditions with one formula so that I can copy it down the column. I tried to nest the 5 text conditions in the IF formula but that doesn't work. I am guessing I am missing something when it comes to placing the multiple FIND functions in the IF function. Basically, if it finds "WHITE", then "GAME WHITE REGION, if "BLUE", then "GAME BLUE REGION", so on and so forth.
Kevin: Nesting would definitely work, but it is tricky to get the parenthesis right. You have to take care when making the formula.
So you start with this:
IF(ISERROR(FIND("BLUE",B2)),B2,"Game Blue Region")
Now replace the second parameter, B2, with a whole other IF statement.
IF(ISERROR(FIND("BLUE",B2)), [whole other if statement goes here] ,"Game Blue Region")
And the result would be:
IF(ISERROR(FIND("BLUE",B2)),IF(ISERROR(FIND("WHITE",B2)),B2,"Game White Region"),"Game Blue Region")
Then you'll need to keep doing that 3 more times to cover your 5 options. It will get long and complicated, but it will work.
Another approach would be to use REGEX.EXTRACT. You can have it look for five words and if it doesn't find them, then output the original B2. If it does find them then output the words GAME and REGION with the found word in the middle.
IF(ISERROR(REGEX.EXTRACT(B2,"(BLUE|WHITE|GREEN|RED|PINK)")),B2,"GAME "®EX.EXTRACT(B2,"(BLUE|WHITE|GREEN|RED|PINK)",)&" REGION")