I’m using a spreadsheet for budgeting and to track my income and expenses, etc. over the course of the year. Come 2021, I would like to delete the data/values, i.e., replace it with zeros, while keeping all the text, formulas, formatting, etc. so I’ll have a fully worked-out and formatted template to work with, but for the actual 2021 numbers. How would I do that?
To clear all numbers and dates use the Find & Replace (Ctrl+H) dialog:
- Find:
^(?:[0-9]+(?:\.[0-9]+)?)|(?:[0-9]+/[0-9]+/[0-9]+)$ - Replace: (leave empty)
- Other options: enable Regular expressions
Hit Replace All, you may want to try Find All first…
If only numbers are to be cleared and dates are to be preserved then use just the expression ^[0-9]+(?:\.[0-9]+)?$
You could also change all year 2020 dates to 2021 with (assuming a DD/MM/YYYY or MM/DD/YYYY date edit format)
- Find:
^([0-9]+/[0-9]+)/2020$ - Replace:
$1/2021
Note: the expressions assume a locale that uses a . dot as decimal separator (the \. part in the optional decimals of the number) and a / slash as date separator. If your locale is different you’ll have to adjust, for example for a , comma decimal separator and . dot date separator it would be
^(?:[0-9]+(?:,[0-9]+)?)|(?:[0-9]+\.[0-9]+\.[0-9]+)$
@erAck Hi - thanks so much for pointing out that regex can be used in Calc! I wasn’t aware of this. I’m somewhat familiar with regex from using the Linux shell, and I see there is also a REGEX function. Will have to dive into this some more, but this is clearly the way to go! Thanks!