I have a large number of months in a spreadsheet I wish to change. I can search and replace ‘/04/’ with ‘/05/’ but it replaces data in ALL the sheets in the file. I want to restrict it to the ONE active sheet. Is this possible without having to click through multiple ‘replace and find’ commands?
—–
Kenneth Taylor
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How Do I Find and Replace In Numbers In a Single Sheet?
Comments: 9 Responses to “How Do I Find and Replace In Numbers In a Single Sheet?”
Comments Closed.
Why not just copy and paste that table into another spreadsheet document. Do your find and replace there, then copy and paste it back. Or, better yet, just the one column with the dates in it.
Thanks Gary, yes I can do that. I hoped there was a feature I was missing. In Excel you can select Sheet or Whole Workbook.
Are there any good pointers for efficiently doing a find and replace for single characters or phrases within a Numbers sheet using Applescript? Part of the issue is I need to loop through the cells of a sheet and examine the values. A find and replace operation should be applied to these cell values.
Tommie: No need to try to figure out AppleScript for that. There are other ways, depending on the details. One method is to use the SUBSTITUTE function. That finds and replaces strings inside of strings. So if B2 contains “This or that” then SUBSTITUTE(B2,”or”,”and”) in cell C2 would result in “This and that.” You can then hide column B and use column C, or copy column C and Edit, Paste Values from C to B and then delete C. Repeat as needed.
Depending on your data, you could also export as CSV and then edit it in a text editor like TextEdit or Text Wrangler and do your replacements there, as text. Then import back into Numbers.
Gary,
Thank you for your reply. In this case, I have a Numbers file that has some commas in different fields that do not behave nicely when I convert the file to csv and import into a different system. So, I've been replacing those with semi-colons. Additionally, I have a couple of stray double quotes and a stray character (�) in the data. My attempt to replace these is because I have 5000 rows per file with hundreds of files to process. Would appreciate any other options.
- Thanks again.
Agree with Gary... copy and paste is great idea if you wan't to click through multiple ‘replace and find’ commands...
Tommie: OK, that's a lot. If it was just a few dozen files, I'd say just use the regular Find & Replace in Numbers. Replace the commas with semicolons, double-quotes, and the stray characters. Repeat for each. But hundreds -- that's tough. I would do it with some code. Not AppleScript, but maybe Python or PHP or whatever. This sounds like a work project, so maybe you have some resources or others in the company that can help? Does this "other system" have any capabilities to do it on that end? Typically the commas shouldn't be an issue as Numbers exports those cells in quotes.
Gary,
I am flying solo (my own business) although I could pay for additional help if I had to. I have already started scripting other changes to the files. Including adding/removing columns. Was just hoping not to have to do a brute force approach where I loop through each cell (unsure how-to) and examine it's value with an "on replacetext" handler. I can code in anything but was hoping that something elegant exists that would make the solution efficient.
Tommie: How about focusing on scripts that make the changes to the exported csv files instead of the Numbers documents. That should be easier, right?