Are you looking at this as a one-off (you originally wrote the formula based on 'ca1' and the conditions have changed?
Or is this something that's likely to change again in the future/frequently?
YellowBox's idea of putting the conditional string in a separate referenced cell makes sense moving forwards, especially if you need to change the string again in the future, but it doesn't help update all the existing formulas to follow the new model.
Unfortunately, as you've found, the standard Find & Replace will not cover this for you since it won't perform a replacement within a formula.
The only two options you have are to walk through the formulas manually, or write some kind of automation using something like AppleScript to walk through the table for you.
Here's an example I threw together (run on a COPY of your spreadsheet, just to be sure). Just paste this into a new Script Editor document and click Run
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
-- this is the key we're looking for in the existing formulas:
property searchKey : "\"ca1\""
-- this is what we want to replace it with:
property replaceKey : "\"dxa1\""
-- note that the above variables use \" to denote a literal quote character
-- without this the script would replace references to cell CA1 with DXA1, which would be wrong
tell application "Numbers Creator Studio"
-- the next three lines identify the document, sheet and table to target. Amend as necessary
tell document 1 -- the frontmost document
tell sheet 1 -- sheet 1... use either sheet number or name
set theTable to table "Table 1" -- best to reference the table by name
-- this loops through every cell in the designated table
repeat with eachCell in cells of theTable
-- let's extract its current formula
set f to eachCell's formula
-- we can ignore cells without formulas
if f is not missing value then
-- does the cell's formula reference our searchKey?
if (f as text) contains searchKey then
-- it does, so replace the search and replace values
set nf to my replaceKeywordInString(f)
-- and reset the cell's formula with the update
set eachCell's value to nf
end if
end if
end repeat
end tell
end tell
end tell
on replaceKeywordInString(sourceString)
-- this function takes a string and does the search/replace heavy lifting, based on manipulating
-- AppleScript's text item delimiters.
set {oldTID, my text item delimiters} to {my text item delimiters, searchKey}
set n to text items of sourceString
set my text item delimiters to replaceKey
set replacedString to n as text
set my text item delimiters to oldTID
return replacedString
end replaceKeywordInString
The comments tell you what each part of the script does, but if you have any questions, feel free to ask.
