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

[Solved] leading quote problem

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Zlad!
Newbie
Newbie


Joined: 18 Mar 2009
Posts: 2

PostPosted: Wed Mar 18, 2009 4:04 am    Post subject: [Solved] leading quote problem Reply with quote

Hi

https://apps.sidma.net/files/b59e2456f5ddb5919e2c0390d4067fd2f1f60112/leading_quote_problem.xls

this is a part from bigger exported document from one accounting software. In B7 i need to calculate how many days difference between B5 and B2, but function B5-B2 says 0 which is wrong
From what I understand it has something to do with number formats and locale setting
http://www.oooforum.org/forum/viewtopic.phtml?t=57522&start=0&postdays=0&postorder=asc&highlight=

because there is ' in front of the dates in input lines, I tried changing my language setting but with no difference.
Also i found that B1 and B4 were set as text format. When i tried changing B4 to number ' appeared in front of it in input line

When I erase ' in input line in B2 and B5 function in B7 when Slovak language is set works good (7) when English it says 182 but that is because of different date formating in US (MM.DD.YY) and in Slovakia (DD.MM.YY) but that is not the solution for me because there are thousands of dates in complete document.

So pls if anybody knows solution for this, I need it until friday or I will have to install Microsoft Office on accountant computer in which it works with no problem


Last edited by Zlad! on Wed Mar 18, 2009 5:42 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 18, 2009 4:54 am    Post subject: Reply with quote

There is text and there are numbers, which has absolutely nothing to do with formatting. Numbers can be formatted in some locale context, text is just literal text. 123 is not the same value as "123" and no formatting will ever change this.
Your software produces text and Calc will never convert text automatically to a number, like it will never contert any number to text by means of any formatting attributes. Your imported values get the leading quote in order to prevent the automatic conversion between data types (again: types have nothign to do with formatting).
You are the only one to change your values.
Convert text to numbers:
Apply any number format you like except for "Text". Now you need to retype everything as you already noticed.
menu:Edit>Find&Replace...
[More Options]
[X]Regular expressions
Search: .+ (dot plus)
Replace: & (ampersant)
[Replace All]
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Zlad!
Newbie
Newbie


Joined: 18 Mar 2009
Posts: 2

PostPosted: Wed Mar 18, 2009 5:42 am    Post subject: Reply with quote

thank you very much this solved my problem and also I learned something
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
Page 1 of 1

 
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