| View previous topic :: View next topic |
| Author |
Message |
aleksandrsa
Newbie
Joined: 18 Oct 2008
Posts: 3
|
 Posted: Sat Oct 18, 2008 8:47 am Post subject: Number auto-format in Calc: how to turn auto format off? |
 |
|
Hi everybody!
Looked through previous threads, but still no solution.
The problem
I enter in a cell the following values (numbered list):
1.1.
1.2. etc.
and Calc immediately changes them to dates (which these numbers are NOT) (I work in Latvian locale)
Then I manually change cell format to text and get:
39448
39479
Wow!
This drives me insane, I don't get the logic and this the ONLY thing that prevents me from using calc as my main spreadsheet application. The same happens always: when I paste lists from Write, when I import CSVs with bank account data... The worst thing is that the result is unfixable, there is no way how to get the source data back (like you see in the example above).
The question
What is the easy solution to turn this ridiculous auto-formatting off?
I'm a regular user, worked with several OOs and NeoOffice on my Mac and faced this problem over and over again.
OO 3 for Mac, Locale: Latvian, Decimal...: same as locale, Default language: Latvian
Thank you in advance for tips on usable solution to this problem. I really like to use OO, but because of this problem I can't switch to it. |
|
| Back to top |
|
 |
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1082
Location: UK
|
| Posted: Sat Oct 18, 2008 10:09 am Post subject: |
|
|
| If you set the cells as text first, then type or paste into them, it will accept whatever you input (I think). |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Oct 18, 2008 10:21 am Post subject: |
|
|
The problem has absolutely nothing to do with formatting.
In your country comma is used as decimal separators, right? Why do you use dots then?
1. is a shortcut for the 1st day of current month (day #39448)
1.2 is a shortcut for the 1st day of current year's February (day #39479)
Since all dates in all spreadsheets are nothing but formatted numbers, you see the real cell values in decimal numbers when you remove the date formatting. Simply use the decimal separator of your locale if you want "3,14159" to be pi or enter literal text if you want "1.2" to be a literal string of two digits separated by a dot.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
Last edited by Villeroy on Sat Oct 18, 2008 10:22 am; edited 1 time in total |
|
| Back to top |
|
|
aleksandrsa
Newbie
Joined: 18 Oct 2008
Posts: 3
|
| Posted: Sat Oct 18, 2008 10:22 am Post subject: |
|
|
| RickRandom wrote: | | If you set the cells as text first, then type or paste into them, it will accept whatever you input (I think). |
Well, yep, tried this:
(1) selecting all worksheet and formatting it as "text";
(2) then paste data from a Write table with numbered list (1.1., 1.2., etc.) and still get dates 
The only thing that worked was "paste special" -> "unformatted text", although in this case I loose table structure and get all cells pasted in a single column |
|
| Back to top |
|
|
aleksandrsa
Newbie
Joined: 18 Oct 2008
Posts: 3
|
| Posted: Sat Oct 18, 2008 10:32 am Post subject: |
|
|
| Quote: | | Why do you use dots then? |
Well, seems I'm not using numbers in this case but text.
I have a table in Wrtie with the structure like that:
A1 | A2 | A3
_________________
1.1. | blablabla1 | yyyytext
_________________
1.2. | blablabla2 | zzzztext
_________________
1.3. | blablabla3 | vvvvtext
"1.1." in this case is the numbered list
What I try to do is to prevent Calc from interpreting two dots after numbers as date... So far only "paste without formatting" worked, BUT the result was a single column with all cells in it, like this:
A1
______
1.1.
______
blablabla1
______
yyyytext
______
1.2.
______
blablabl2..... etc.
_______ |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Mon Oct 20, 2008 5:34 am Post subject: |
|
|
| aleksandrsa wrote: | | Quote: | | Why do you use dots then? |
Well, seems I'm not using numbers in this case but text.
I have a table in Wrtie with the structure like that:
A1 | A2 | A3
_________________
1.1. | blablabla1 | yyyytext
_________________
1.2. | blablabla2 | zzzztext
_________________
1.3. | blablabla3 | vvvvtext
"1.1." in this case is the numbered list
What I try to do is to prevent Calc from interpreting two dots after numbers as date... So far only "paste without formatting" worked, BUT the result was a single column with all cells in it, like this:
A1
______
1.1.
______
blablabla1
______
yyyytext
______
1.2.
______
blablabl2..... etc.
_______ |
Try exporting the Writer document (or the table part anyway) as a csv file and import it into Calc. Calc should then give you the option of setting the first column to text.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Oct 20, 2008 6:08 am Post subject: |
|
|
Paste special as unformatted text. In the import wizard right-click the column with dotted numbers and mark them as "Text".
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
snutz
Newbie
Joined: 04 Nov 2009
Posts: 2
|
| Posted: Wed Nov 04, 2009 2:07 pm Post subject: Same Problem...solution! |
|
|
| I was just struggling with the same problem. Turns out if you put an apostrophe before the number, e.g. '1.2.3, it will display it correctly (and without the apostrophe). |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Nov 05, 2009 8:23 am Post subject: Re: Same Problem...solution! |
|
|
| snutz wrote: | | I was just struggling with the same problem. Turns out if you put an apostrophe before the number, e.g. '1.2.3, it will display it correctly (and without the apostrophe). |
Could you please tell me what kind of number "1.2.3" is supposed to be? Is it an IP address or something?
When you enter "1.2.3" with apostrophe you do not get any number at all. You get a text value.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
snutz
Newbie
Joined: 04 Nov 2009
Posts: 2
|
| Posted: Thu Nov 05, 2009 12:43 pm Post subject: Re: Same Problem...solution! |
|
|
| Villeroy wrote: | | snutz wrote: | | I was just struggling with the same problem. Turns out if you put an apostrophe before the number, e.g. '1.2.3, it will display it correctly (and without the apostrophe). |
Could you please tell me what kind of number "1.2.3" is supposed to be? Is it an IP address or something?
When you enter "1.2.3" with apostrophe you do not get any number at all. You get a text value. |
The number is nothing special (a software version in my case), I just was having trouble getting it to display as 1.2.3. Before I added the ' before the numbers, Calc was showing 01/02/03 as a date. A text value was what I want to see. |
|
| Back to top |
|
|
taur
Newbie
Joined: 31 Aug 2011
Posts: 1
|
| Posted: Wed Aug 31, 2011 11:14 pm Post subject: |
|
|
2 years later, still having this issue
making ordered task-lists numbering items
the preceding ' thing works as a workaround for now |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Sep 01, 2011 1:40 am Post subject: |
|
|
This topic is ridiculous. Each and every software distinguishes between data types, numbers and text in particular. This is how computers use to work ever since and how they will work for the next decade.
And snut wants his software versions as text "1.2.3". A software version "1.2.3" is a text containing 3 numbers and 2 points. In English context there can not be a number with 2 points. It may be interpreted as a date in German language context (1st of 2nd of year 3 => 01/Feb/2003).
Calc/Excel/Gnumeric and many other spreadsheets accept any data type in every single cell, which is a wanted behaviour. This makes it inevitable, that all keyboard input and text import as well needs to be interpreted one way or ther other. All the mentioned programs give you full control over the data types. They all do not do one thing: They do not auto-format. They do not format any number according to what you entered. THAT would be auto-formatting and the software would not be usable anymore.
Either you do the formatting by your own or the software applies one particular format for any detected integer, decimal, date, time, percent, currency.
In case of text input number formats are pointless.
If you want something different, you must not use spreadsheets.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
nickel
Newbie
Joined: 23 Aug 2011
Posts: 3
|
| Posted: Thu Sep 01, 2011 7:20 am Post subject: |
|
|
Villeroy, not topic - this feature is ridiculous. Don't take it personal, though)
I use calc for many purposes and still wondering the same question as aleksandrsa does. When I paste data in Calc I can't add ' to all cells, where the data should be treated as text, but I can define type of cells as "text" before I paste. What actually Calc succesfully ignores and changes my data according to its own vision. |
|
| Back to top |
|
|
gerard24
OOo Enthusiast
Joined: 08 Jul 2011
Posts: 100
Location: France
|
| Posted: Thu Sep 01, 2011 9:11 am Post subject: |
|
|
When you paste, you paste all . Your text format is erased.
Use paste special (Ctrl+Shift+V) as unformatted text.
_________________
LibreOffice 3.5.0 on Windows Vista |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Sep 01, 2011 9:47 am Post subject: |
|
|
| nickel wrote: | Villeroy, not topic - this feature is ridiculous. Don't take it personal, though)
I use calc for many purposes and still wondering the same question as aleksandrsa does. When I paste data in Calc I can't add ' to all cells, where the data should be treated as text, but I can define type of cells as "text" before I paste. What actually Calc succesfully ignores and changes my data according to its own vision. |
No, it doesn't. That's all I can tell. You don't even tell us your software version nor what you do exactly.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
