[cloudburst7]

I have a table in which the first column is numbers. Sometimes there is a range eg 3-4 in which Calc auto changes it to a date eg 3-4 becomes 03/04/08

I've tried rummaging through its menus but can't find the solution. Changing the date format for a particular cell makes that cell look ok, but I'd have to do that for individual cells becuase copying it to the whole column spoils those cells which contain a single digit, not a range.

I'm sure there's an obvious solution, but right now I can't find it. Any help will be much appreciated!



Edited to add:

Changing the cell format to text, or All, to avoid it being read as a number, does somethign even stranger, eg 4-5 is changed to 39572

[RootbeaR]

Originally Posted by cloudburst7

I have a table in which the first column is numbers. Sometimes there is a range eg 3-4 in which Calc auto changes it to a date eg 3-4 becomes 03/04/08

I've tried rummaging through its menus but can't find the solution. Changing the date format for a particular cell makes that cell look ok, but I'd have to do that for individual cells becuase copying it to the whole column spoils those cells which contain a single digit, not a range.

I'm sure there's an obvious solution, but right now I can't find it. Any help will be much appreciated!



Edited to add:

Changing the cell format to text, or All, to avoid it being read as a number, does somethign even stranger, eg 4-5 is changed to 39572

Use an apostrophe before the number.
eg. * '4-5 * instead of * 4-5 *

[cloudburst7]

Thank you - that will help when inputting more data or creating new tables in the future.

However I've got a full table worth of data already - is there a quick way to put an apostrophe before all the numbers then? Or must I go through the table manually changing all the affected cells?

[RootbeaR]

Originally Posted by cloudburst7

Thank you - that will help when inputting more data or creating new tables in the future.

However I've got a full table worth of data already - is there a quick way to put an apostrophe before all the numbers then? Or must I go through the table manually changing all the affected cells?

That I don't know.

[Zack Barresse]

If you have Excel, and the ASAP Utilities add-in, there is. Do you have Excel to do this? You could post it up here and someone could do it for you, I'm sure. Wouldn't take very long at all.

[slurpee55]

Excel does the same thing - it thinks 3-4 is a date, and both programs hold dates as a # of days since 1/1/1900 (I think that is the base date) so that is what the 4-5 becoming 39572 means.
The following directions really are based on Excel, but I am sure they will work in Calc, although the specific commands may be a little different.
For a workaround, first format your cells that are showing the date as Format, Cells, Special. In the bar at the top, enter "m-d;@" without the "" around it.
Your text will now appear as 3-4. However, Calc still thinks it is a date, so you might prefer to go one more step. Copy all that data, open up notepad and paste it in. Notepad sees everything as text, so that is now the way it is formatted. In Calc, format your column (or another if you prefer to be able to compare the results) as text, by highlighting it and using Format Cells, Text.
Copy or cut all the data from Notepad and paste it into the text-formatted column in Calc.