Page 1 of 1

Bad Numbers Software?

PostPosted: Wed May 02, 2012 8:46 am
by RexinMinn
I am using a spreadsheet in Numbers.

One of my columns contains a list of the number of baseball games won and lost at home from 1902-15, for example, the notation 2-1 means the team won two games and lost one. The list changes as more games are played, so the next cell down might be 3-1, then 4-1, then 4-2, etc.

The problem is that occasionally I enter data in a cell in which the notation 2-1 (just an example) will appear as a date, such as Feb. 1, 2012.

I then go to the inspector to change the entire column (which I highlight by clicking on the letter at the top of the column) and change the cell format to "TEXT." But that doesn't work. So I have to click on "DATE" and that changes all 300 cells in the column to a date. I then go back and convert to "TEXT" again. But this time it only converts it back to a numerical format of the same date that was in the cells as a result of the previous step.

I cannot get the format to revert to the orignal I had, and now I must re-do each cell individually. This will take at least a half-hour, and I am very unhappy about this. I suspect there is an inherent flaw in Numbers here. What do you think and do you have any suggestions for how I can avoid this in the future?

I hope I'm explaining myself clearly, but if I'm not, please let me know, as I would hope there would be some kind of solution to this problem.

Re: Bad Numbers Software?

PostPosted: Wed May 02, 2012 12:19 pm
by Peggy
Set the format to text before you enter any data.

I've set up my own blank Numbers template with a table that has all cells formatted as text. It's easy to change from text to another format but not vice versa.

Re: Bad Numbers Software?

PostPosted: Thu May 03, 2012 3:11 pm
by Barry
With the cells set to Automatic, Numbers is recognizing your entries as a date, and finishes the Date and Time value, just as it would if you entered 'Feb 2'. Handy in some cases, but like most automatic processes, a nuisance in others.

You can retrieve the original entry using a (temporary) auxiliary column with the formula below.

Data misinterpreted as dates is in column B.

Click on B2 and press option-right arrow to add a new column C to hold the formula.

Enter in C2: =IFERROR(MONTH(B)&"-"&DAY(B),""&B)    (The "&" is this font's 'ampersand' character.)

Fill the formula down the rest of the column.

The formula extracts the month and day from Date and Time values in column B and presents the result as a text string. Noon-date and Time values will cause MONTH and DAY to throw an error. IF ERROR catches the error, and presents the value in B, converted to a text string by appending a null string ahead of it ( ""&B ).

After converting:
    select the cells containing data in C and Copy
    Select the cells in B and set the format to Text.
    Go Edit (menu) and choose Paste Values.
    Select column C and go Table (menu) > Delete Column.