ChrisWilliams
Level 1 (8 points)
iWork

Q: Numbers spreadsheet - auto reformat

In my Numbers spreadsheet I want "7.9 sec" in the cell (without the quotation marks) but Numbers keeps converting it to "7s 900ms".  How do I get Numbers to stop converting the format automatically?

iMac, OS X Yosemite (10.10.3), 3GHz Intel Core 2 Duo

Posted on Aug 20, 2016 8:22 PM

by Yellowbox,Solvedanswer
Yellowbox Yellowbox
Level 6 (10,550 points)
Mac OS X
A:

Hi Chris,

I may as well put "seconds" in the column header, and format the cell contents as a number with 1 decimal.

That will work (column B)

Screen Shot 2016-08-21 at 6.56.00 PM.png

Or, a Custom Format

Screen Shot 2016-08-21 at 6.57.28 PM.png

Screen Shot 2016-08-21 at 6.58.47 PM.png

That includes integer, decimal, space, sec but Numbers treats it as a number, not text.

 

Regards,

Ian.

Posted on Aug 21, 2016 2:03 AM

Close
ChrisWilliams

Q: Numbers spreadsheet - auto reformat

  • All replies
  • Helpful answers

  • by Yellowbox,Helpful

    Yellowbox Yellowbox Aug 20, 2016 9:56 PM in response to ChrisWilliams
    Level 6 (10,550 points)
    Mac OS X
    Aug 20, 2016 9:56 PM in response to ChrisWilliams

    Hi Chris,

    Try this

    Screen Shot 2016-08-21 at 2.24.38 PM.png

    Regards,

    Ian.

  • by t quinn,Helpful

    t quinn t quinn Aug 20, 2016 9:57 PM in response to ChrisWilliams
    Level 5 (5,063 points)
    Mac OS X
    Aug 20, 2016 9:57 PM in response to ChrisWilliams

    Hi Chris,

     

    I think Ian has got you as close as you can get. I don't see a way to add "sec" to the end or to eliminate the extraneous zeros.

     

    quinn

  • by ChrisWilliams,

    ChrisWilliams ChrisWilliams Aug 20, 2016 10:12 PM in response to ChrisWilliams
    Level 1 (8 points)
    iWork
    Aug 20, 2016 10:12 PM in response to ChrisWilliams

    Thanks.  I never noticed the [Duration] menu under [Date & Time] before.

     

    It's close to what I want.  I am trying to format the numbers as they appear on a web page, and since I am copying and massaging much of the data by hand, I may as well put "seconds" in the column header, and format the cell contents as a number with 1 decimal.

  • by ChrisWilliams,

    ChrisWilliams ChrisWilliams Aug 20, 2016 10:23 PM in response to t quinn
    Level 1 (8 points)
    iWork
    Aug 20, 2016 10:23 PM in response to t quinn

    It's curious though - this screen capture from an earlier version of the SS:ScreenShot_2.jpg

    Line 43 is the one I am having trouble with - two entries got loaded into the same cell.  When I try to split the numbers into separate cells I get the auto reformat.  Line 42 (and 46, 47 & 48) all have some invisible character after "sec" which (I think) convinces Numbers that the entry is text only.  That is the format I am trying to duplicate on line 43.

  • by Yellowbox,Solvedanswer

    Yellowbox Yellowbox Aug 21, 2016 2:03 AM in response to ChrisWilliams
    Level 6 (10,550 points)
    Mac OS X
    Aug 21, 2016 2:03 AM in response to ChrisWilliams

    Hi Chris,

    I may as well put "seconds" in the column header, and format the cell contents as a number with 1 decimal.

    That will work (column B)

    Screen Shot 2016-08-21 at 6.56.00 PM.png

    Or, a Custom Format

    Screen Shot 2016-08-21 at 6.57.28 PM.png

    Screen Shot 2016-08-21 at 6.58.47 PM.png

    That includes integer, decimal, space, sec but Numbers treats it as a number, not text.

     

    Regards,

    Ian.

  • by ChrisWilliams,

    ChrisWilliams ChrisWilliams Aug 21, 2016 4:23 PM in response to Yellowbox
    Level 1 (8 points)
    iWork
    Aug 21, 2016 4:23 PM in response to Yellowbox

    Thanks, Ian - that works!