OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Number auto-format in Calc: how to turn auto format off?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
aleksandrsa
Newbie
Newbie


Joined: 18 Oct 2008
Posts: 3

PostPosted: Sat Oct 18, 2008 8:47 am    Post subject: Number auto-format in Calc: how to turn auto format off? Reply with quote

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
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sat Oct 18, 2008 10:09 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Oct 18, 2008 10:21 am    Post subject: Reply with quote

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 https://forum.openoffice.org


Last edited by Villeroy on Sat Oct 18, 2008 10:22 am; edited 1 time in total
Back to top
View user's profile Send private message
aleksandrsa
Newbie
Newbie


Joined: 18 Oct 2008
Posts: 3

PostPosted: Sat Oct 18, 2008 10:22 am    Post subject: Reply with quote

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 Sad

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
View user's profile Send private message
aleksandrsa
Newbie
Newbie


Joined: 18 Oct 2008
Posts: 3

PostPosted: Sat Oct 18, 2008 10:32 am    Post subject: Reply with quote

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
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Mon Oct 20, 2008 5:34 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Oct 20, 2008 6:08 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
snutz
Newbie
Newbie


Joined: 04 Nov 2009
Posts: 2

PostPosted: Wed Nov 04, 2009 2:07 pm    Post subject: Same Problem...solution! Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Nov 05, 2009 8:23 am    Post subject: Re: Same Problem...solution! Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
snutz
Newbie
Newbie


Joined: 04 Nov 2009
Posts: 2

PostPosted: Thu Nov 05, 2009 12:43 pm    Post subject: Re: Same Problem...solution! Reply with quote

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
View user's profile Send private message
taur
Newbie
Newbie


Joined: 31 Aug 2011
Posts: 1

PostPosted: Wed Aug 31, 2011 11:14 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Sep 01, 2011 1:40 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
nickel
Newbie
Newbie


Joined: 23 Aug 2011
Posts: 3

PostPosted: Thu Sep 01, 2011 7:20 am    Post subject: Reply with quote

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
View user's profile Send private message
gerard24
OOo Enthusiast
OOo Enthusiast


Joined: 08 Jul 2011
Posts: 100
Location: France

PostPosted: Thu Sep 01, 2011 9:11 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Sep 01, 2011 9:47 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group