How Do I Find and Replace in a Formula with Numbers

Question

Level 1

9 points

How Do I Find and Replace in a Formula with Numbers

Numbers has a "find and replace" feature but it only seems to apply for text, not formulas. In Excel, I can find and replace within the formula. For example, in excel, if I have a row with each cell in that row referring to another cell (for example, =G47, =H47, =I47, =J47, etc.), I could highlight that row and and use the find and replace function to change the "7" too and "8". The result would be a row with =G48, =H48, =I48, =J48, etc. Is it possible to use find and replace within a formula using Numbers? Thanks

MacBook Air 13", macOS 10.14

Posted on Jun 10, 2019 7:05 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,041 points

Jun 10, 2019 12:20 PM in response to mrrworld

Not directly.

But if your real situation is well represented by your example, you might not need as much effort as you think.

For example, in excel, if I have a row with each cell in that row referring to another cell (for example, =G47, =H47, =I47, =J47, etc.), I could highlight that row and and use the find and replace function to change the "7" too and "8". The result would be a row with =G48, =H48, =I48, =J48, etc.

Double-click the cell containing the formula "=G48" to open the Formula Editor.
Click the G48 token to select it. Pause, then click it a second time to place the insertion point in the text.
Move the insertion point to the right of "8", press delete once to delete 8, type 7 to insert 7.
Click the green checkmark to confirm the revised formula and close the formula editor.
With the cell still selected, hover the pointer near the right edge of the cell.
Click and drag the yellow fill control to fill the edited formula right into the rest of the cells to be changed.

Regards,

Barry

Reply

Answer 2

Barry

Level 9

60,041 points

Jun 10, 2019 12:33 PM in response to Barry

Addendum:

It is possible to convert ALL formulas in a table to text using an AppleScript, use Find/Replace to do the replacement, then convert the text versions back to formulas using a second script.

The process would run into a second shortcoming of Find/Replace wrt your proposed process:

"I could highlight that row and and use the find and replace function to change the "7" too and "8". "

FIND/Replace does not provide an opportunity to restrict the search in this manner. It searches the whole document, not just "that row" (or even just "that table".

Take a look at this discussion, especially to SGIII's post:

https://discussions.apple.com/thread/250386873

Regards,

Barry

Reply

Answer 3

mrrworld Author

Level 1

9 points

Jun 10, 2019 12:54 PM in response to Barry

Thanks so much. This worked well although still not as easy or quick as with Excel. I have some complex spreadsheets including one with 13 different worksheets, all linked to each other, so this fix was better than what I had been doing. As far as your addendum, I did try to convert to text and then back but I could not get around the problem of it searching the entire document, which quickly became a problem.

Are you aware of any decent tutorials on using Numbers that get beyond the basics? I've never used Numbers before but I have an older Mac with even older Excel software. Everytime I upgrade my Mac operating system, Excel gets worse and worse or slower and slower, so I thought it was better to switch to Numbers. I've been able to self teach myself using the help function but i run into problems all the time. Thanks again for your response.

Reply

Answer 4

Barry

Level 9

60,041 points

Jun 10, 2019 1:39 PM in response to mrrworld

Many (possibly most) of the video tutorials you'll find online apply to Pages '09 (aka Pages v4), an earlier version. Pages v5 and later is a totally rewritten application that shares a name, but not much else with the earlier versions.

I've read most of the online sample of Take Control of Numbers, a 280 page ebook available online. From what I've read, it's a step above others in the field wrt to clarity and understanding. The sample is substantial enough to get a good idea of whether you'd find it useful.

For specific questions, you best tutorial is here.

Regards,

Barry

Reply