How to clear data but not formula from a cell in numbers 3.6 for os x

Here are some other ideas. Let's say you start with a table like this, with formulas in column D and row 6.

You can select the cells with formulas and apply a Custom Format to hide values of 0 or less, like this:

You define a Custom Format in the Data Format dropdown list in the Format > Cell panel.

You can use the script below to clear the contents of cells and leave just formulas, formats and text values (No AppleScript knowledge required; just copy-paste, select, and click).

1. Copy-paste into Script Editor (in Applications > Utilities).
2. Select the cells in the table that you want to clear.
3. Click the 'run' button.

The table will now look like this:

But the formulas are still there.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

--set selRng to selection range

repeat with aCell in (get selection range)'s cells

try

tell aCell to if its formula is missing value and ¬

(its value) * 1 is its value then ¬

set its value to ""

end try

end repeat

end tell

end tell

"Several cells in the future calculate non-meaningful results, because they require data for a certain date. So I want the formatting, and the formula, but I don't want the cell populated with a zero (or the result of 0 less another cell)."

Hi nelsolot,

You could add a 'switch' to the formula in these cells that suppresses calculation and inserts a null string until the required data has been entered.

Example: Required data will be entered in B2.

=IF(B2="","",existing-formula)

or

=IF(LEN(B2)<1,"",existing-formula)

Regards,

Barry

Can you post a screenshot? (I use Excel too but can't figure out what you are trying to replicate in Numbers.)

SG