Find/replace all formulae of a certain type in a given column?

[appreciatethehelp]

LO 6, OS Mojave

Hi all, I want to use Find and Replace to amend formulae in a given column, however I don’t know how to select all formulae of a certain type in a particular column in the Find & Replace dialog, only specific formulae pertaining to just 1 cell.

E.g. Say I have a sheet with two columns (A and B) and three rows, and there is a formula in all three rows of column B that equals the adjacent number in cell A minus 1 ["=A1-1", “=A2-1” and “=A3-1”], how do I specify, when searching for the formula to replace in the Find & Replace dialog, that I want to find & replace ALL formulae of the “column A minus 1” variety in column B, rather than just 1 instance of this formula? What would the formula search in the Find & Replace dialog look like in this instance?

Many thanks.

[erAck]

It would probably be more straight forward if you explained what you actually want to achieve. I guess you want to change all formulas to a similar pattern, so changing the formula in B1 and then copy-pasting that down would be the solution.

[Grantler]

For your example it could do (=A[:digit:]+)(-1) (Regular Expressions activated; selection column B; parentheses for better replacing).

@erAck wrote: if you explained what you actually want to achieve - good argument!

[erAck]

To find all formulas =A… select the column or cell range in B and in Find&Replace enter

• Find: ^=A[0-9]+
• Other Options:
• enable Current selection only
• enable Regular expressions

To replace formulas using the original reference (here for example to subtract 23 instead of 1)

• Find: ^=(A[0-9]+).*
• Replace: =$1-23

where the expression part in () parentheses groups a match (here the cell references) that in the replacement can be used by the $1 expression, here 1 for the first (and in this case only) group.