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

Help .. Convert to Number
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
myOo
Newbie
Newbie


Joined: 18 Jan 2006
Posts: 3
Location: Indonesia

PostPosted: Wed Jan 18, 2006 9:42 pm    Post subject: Help .. Convert to Number Reply with quote

Hwo convert to number in Calc..?

Ms. Office convert to number with paste sepicial, in Oo i can conver text to number.

Example

in Ms. Exel : '999 --> 999
in Oo : '999 --->0

Please help me friend..!
_________________
myOo
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Wed Jan 18, 2006 11:24 pm    Post subject: Reply with quote

Hi myOo!
Excel can interpret string as a number if it is possible. For OOo string is a string and for calculations it has to be converted to numeric type by user!
Let's say that you have '999 in cell A1. Put formula
Code:
=VALUE(A1)
in some other cell and there you will get a number wich can be calculated.
If you want to have this new numeric data only, copy this cell, then paste special (deselect Formula). Now original data can be deleted.
Uros
Back to top
View user's profile Send private message
asoler
General User
General User


Joined: 19 Jan 2006
Posts: 8

PostPosted: Thu Jan 19, 2006 9:34 am    Post subject: Reply with quote

I have the same problem, when a number have apostrophe before.

When I use search apostrophe to replace, I don`t have result.

I hope this is a bug.
Back to top
View user's profile Send private message MSN Messenger
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Thu Jan 19, 2006 11:17 am    Post subject: Reply with quote

asoler wrote:
I have the same problem, when a number have apostrophe before.

When I use search apostrophe to replace, I don`t have result.

I hope this is a bug.


Another approach if a single shee, not too complicatedt: Save as CSV. In the options for text delimiters, delete the quotation marks ["] before saving. Then Open the CSV in Calc and save as a Calc file.

David.
Back to top
View user's profile Send private message
asoler
General User
General User


Joined: 19 Jan 2006
Posts: 8

PostPosted: Thu Jan 19, 2006 11:29 am    Post subject: Reply with quote

I will try this.

But if you have a spreadsheet with a cell with (for example): '3 and if i try to find ' there is no return value.
Back to top
View user's profile Send private message MSN Messenger
DiGro
Super User
Super User


Joined: 02 Jun 2004
Posts: 1415
Location: Hoorn NH, The Netherlands

PostPosted: Thu Jan 19, 2006 11:42 am    Post subject: Reply with quote

That is true.

the ' is the indicator for text. It is the same in Excel.

so Calc sees everything behind the ' as text, not numbers
_________________
DiGro

Windows 7 Home Premium and AOO 4.0.1 NL (Dutch)
Back to top
View user's profile Send private message
asoler
General User
General User


Joined: 19 Jan 2006
Posts: 8

PostPosted: Thu Jan 19, 2006 11:48 am    Post subject: Reply with quote

Ok, but en excel if you try find ' you could. Here i can`t find and replace.
Back to top
View user's profile Send private message MSN Messenger
carl
Super User
Super User


Joined: 21 Apr 2003
Posts: 920
Location: Germany

PostPosted: Fri Jan 20, 2006 1:38 am    Post subject: Reply with quote

I recommend you follow Davids suggestion.
Save the sheet as a csv and then re-open it. Ijust tried it and they were gone.
If that doesnt do the trick then open the csv in Metapad (freeware http://liquidninja.com/metapad/ ) and use the very simple find and replace function, save again and re-open in calc.
_________________
carl
Using OpenOffice.org 2 on XP sp2
Back to top
View user's profile Send private message
asoler
General User
General User


Joined: 19 Jan 2006
Posts: 8

PostPosted: Fri Jan 20, 2006 2:39 am    Post subject: Reply with quote

Yes, i will do the cvs and reopen. But it isn`t the right way. I have a lot of hard users, and i'm testing openoffice to leave M$ platform. I can`t say to the users "save as csv, modify the file, and then reopen"

This bug i've detected in many imported excel files and csv files.

In csv files, when i import i specify the column as a number format. In many cases the number appear as a text with '. Excel have the same effect, but i can replace ' with nothing and then work.

Try this, put in a cell this value: '''''' then try to find ' with search & replace. Not work. Or try to find a cell with " the same result.
Back to top
View user's profile Send private message MSN Messenger
carl
Super User
Super User


Joined: 21 Apr 2003
Posts: 920
Location: Germany

PostPosted: Fri Jan 20, 2006 3:37 am    Post subject: Reply with quote

yes you are right. you need to check out Regular Expressions in Find and replace
_________________
carl
Using OpenOffice.org 2 on XP sp2
Back to top
View user's profile Send private message
asoler
General User
General User


Joined: 19 Jan 2006
Posts: 8

PostPosted: Fri Jan 20, 2006 4:18 am    Post subject: Reply with quote

I have probing all alternatives, with and without regular expressions, with hexa codes, with slash, etc....
Back to top
View user's profile Send private message MSN Messenger
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Jan 20, 2006 9:02 am    Post subject: Reply with quote

Search for "^." and replace with "&". Check Regular Expressions.

This appears to convert text numbers to numbers and leave everything else as is or is that as was. Have no idea why it works.
Back to top
View user's profile Send private message
asoler
General User
General User


Joined: 19 Jan 2006
Posts: 8

PostPosted: Fri Jan 20, 2006 9:30 am    Post subject: Reply with quote

Not work
Back to top
View user's profile Send private message MSN Messenger
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Jan 20, 2006 12:38 pm    Post subject: Reply with quote

What can I say except it works for me in 2.0.1 & 1.1.5. Please note that my test file was an OOo file and not something like an Excel file opened in OOo. I'd try one of those but don't have MS Office.

What I did not mention is that if you have cells that have been formatted as Text with Format > Cell > Number tab or the like then those cells must first be reformatted to Number. In my little test file Format > Default Formatting was sufficient to get this done.
Back to top
View user's profile Send private message
NoOneLeft
General User
General User


Joined: 20 Jan 2006
Posts: 13

PostPosted: Fri Jan 20, 2006 7:10 pm    Post subject: Reply with quote

I get the same results as asoler. Find & Replace ignores the '

Last edited by NoOneLeft on Fri Jan 20, 2006 7:17 pm; edited 1 time in total
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, 3  Next
Page 1 of 3

 
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