using numbers how can I get

photo,

I submit the easiest way is to prefill the table with dashes, then enter everything else after. If there volume of cells favors "non-dash" data, then prefill a new table with dashes, then command click the various cell ranges, then paste the actual data into the new table.

Hi Phil,

In Numbers (or any spreadsheet) you can't "push" a value into a cell. You can "pull" a value into a cell.

In the bottom Table is a formula that pulls a value from the top Table, or if a cell is blank, inserts a hyphen:

Insert this formula into Cell A1 of the bottom Table:

=IF(Table 1 :: A1="","-",Table 1 :: A1)

In English,

IF Table 1 Cell A1 is Null ("") THEN insert -, ELSE insert the value from Table 1 Cell A1

Then Fill right and down.

Regards,

Ian.

Hi Phil,

Ian's method will give you a second table with the appearance you want, and the second table will update automatically as data is added or revised in the first.

If some of the data in Table 1 is created by formulas in the cells, then this is probably the best method to use. I would suggest constructing the second table folowing the directions below.

Select the original table by clicking its icon in the Sheets list at the left side of the document.

Press Command-D to duplicate that table.

Click on Cell A1 of the duplicate table, then enter the formula below:

=IF(LEN(Table 1 :: A1)>0,Table 1 :: A1,"-")

Click the Accept button or the green checkmark to confirm the entry (and to keep cell A1 selected).

Copy.

Shift-click on the bottom right cell in the duplicate table to select all cells in that table.

Go Edit > Paste and Match Style.

(Optional) Click the "Center text" button in the format bar to center the text (including the hyphens) in the cells.

Result:

If all of the (filled) cells in the original table contain directly entered data (ie. typed, pasted or inserted, but not determined by a formula in the cell), then I'd still go with this variation on Ian's suggestion, but would include the following steps:

With all cells in the duplicate selected, Copy.

Click on cell A1 of the original table.

Go Edit > Paste and match style.

Click on the duplicate table's icon in the Sheet's list to select that table.

Press delete to delete the duplicate table.

Regards,

Barry

I can't think of a way to do it in Applescript, I'm not sure how to detect a blank cell. A blank cell using Applescript has a value of zero, same as a cell that actually has a zero in it or a formula that equates to zero.

I am assuming your table has formulas you want to preserve, not just text data. Here is one idea but it will remove whatever special cell formatting you might be using in the cells of your table. It will end up with "automatic" as the cell format for all cells in the table :

1. Select your entire table and format it as text. This will turn your formulas into text.
2. Follow Yellowbox's suggestion to create a new table that uses IF to put hyphens in blank cells
3. Select that entire table (click on cell A1, then use Cmd-A to select the rest of the cells)
4. Copy
5. Click on cell A1 of your original table
6. Paste Values (not the normal Paste operation, it is in the Edit menu)
7. Format your original table as Automatic. The formulas should convert back to formulas instead of text.

Hello

You may also try something like the following script. Select the target range, run the script and it will fill every blank cell in selection with "-". It will take a while if the target range contains many blank cells because it will set the value of each cell one by one.

Note that this script will briefly change the format to text and then undo it in order to probe empty cells. It is annoying but necessary to work around a defect of Numbers 09's scripting interface.

Regards,

H

_main()
on _main()
    script o
        property fill_value : "-"
        property bb : {}

        on _undo()
            tell application "Numbers" to activate
            tell application "System Events"
                tell process "Numbers"
                    keystroke "z" using {command down}
                end tell
            end tell
        end _undo

        tell application "Numbers"
            set {range:_range} to my _selection(document 1)
            if _range is missing value then return

            tell _range
                set format to text
                tell (cells whose value = 0.0) -- [1]
                    if exists then set bb to name
                end tell
                my _undo() -- undo format change

                repeat with b in my bb
                    set cell b's value to fill_value
                end repeat
            end tell
        end tell
        (*
            [1] Empty cell is represented as 0.0 while number 0.0 is represented as "0.0" when format is text.
                This behaviour may let us distinguish empty cell from cell containing 0.0.
        *)
    end script
    tell o to run
end _main

on _selection(doc)
    (*
        reference doc : target document
        return record : {range:_range, table:_table, sheet:_sheet}
            _range = reference to named range in selection
            _table = table object to which selection range belongs
            _sheet = sheet object to which selection range belongs
    *)
    (*
        Limitation
            Numbers allows to select uncontinuous regions
            but its scripting interface does not provide decent method to retrieve them.

            If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
            which includes all the regions in selection.
    *)
    script o
        property parent : {}
        property pp : {}
        local q, r, s, _range, _table, _sheet
        tell application "Numbers"
            set pp to doc's every sheet's every table's selection range as list
            repeat with p in my pp -- per sheet
                set q to p's every reference -- retrieve object (filtering out missing value)
                if q ≠ {} then
                    set q to q's item 1 -- selection range object [1]
                    set r to q as record -- selection range object specifier record [2]
                    set _table to r's every reference's item 1 -- container table reference [3]
                    set s to (a reference to _table's selection range) -- selection range reference [4]
                    set _range to (a reference to _table's range (s's name)) -- named range reference [5]
                    set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
                    return {range:_range, table:_table, sheet:_sheet}
                end if
            end repeat
            return {range:missing value, table:missing value, sheet:missing value}
        end tell
        (*
            [1] class specifier for 'range' is broken in Numbers 09
            [2] «class want» value is broken in Numbers 09
            [3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
            [4] proper reference of selection range object
            [5] proper reference of named range object
        *)
    end script
    tell o to run
end _selection

Hi Phil,

Barry's solution using the LEN() function will detect a Cell that has greater than zero length (that is, a Cell containing a space or some other invisible character).

From the Function Browser in Numbers:

"The LEN function returns the number of characters in a string".

Barry's solution will help by detecting a Cell that appears to be blank but is not really empty.

Regards,

Ian.