What is the formula to set value of all cells in a spreadsheet’s column D to “X”, if the adjacent cell in column C matches regex /.ABC./?
=IF(ISNA(REGEX(C5;"/.ABC./";;1));"";"X") (case sensitive)
=IF(ISNA(REGEX(C5;"(?i)/.ABC./";;1));"";"X") (case insensitive)
A general hint: Ordinary spreadsheet formulas return a result to the cell they are contained in. Examples posted her mostly use arbitrary references that need to be adapted to the specific needs of the user.
When filling down (e.g.) or copy/pasting a formula references are adapted automatically if not made absolute by the “$” prefix. Most standard functions accept range references in place of single references and will then “iterate” the evaluation through the range. Depending on the specifics they may then return an array and lock the output range. To get this array-evaluation, the formula must be entered using the Ctrl+Shift+Enter key-grip. (Some disadvantages!)
“All cells in a spreadsheet” are 1073741824 exactly. You won’t want to apply a formula to them at once - and if you tried you would need very much RAM and have to wait for the answer about a day. The answer might be 42 then.
Column C -as any column- has 1048576 cells.
Hi,
Why C5? I want this to be applied to every row, not just one row.
Please add a comment don’t use answers.