Ask Your Question

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

asked 2020-07-16 11:58:48 +0200

appreciatethehelp gravatar image

updated 2020-08-05 00:53:56 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete


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.

erAck gravatar imageerAck ( 2020-07-16 12:15:43 +0200 )edit

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!

Grantler gravatar imageGrantler ( 2020-07-16 12:22:18 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-16 12:33:24 +0200

erAck gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-16 11:58:48 +0200

Seen: 51 times

Last updated: Jul 16