[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 Help .. Convert to Number Goto page 1, 2, 3  Next
Author Message
myOo
Newbie

Joined: 18 Jan 2006
Posts: 3
Location: Indonesia

 Posted: Wed Jan 18, 2006 9:42 pm    Post subject: Help .. Convert to Number 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
uros
Super User

Joined: 22 May 2003
Posts: 601
Location: Slovenia

Posted: Wed Jan 18, 2006 11:24 pm    Post subject:

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
asoler
General User

Joined: 19 Jan 2006
Posts: 8

 Posted: Thu Jan 19, 2006 9:34 am    Post subject: 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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Thu Jan 19, 2006 11:17 am    Post subject:

 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.
asoler
General User

Joined: 19 Jan 2006
Posts: 8

 Posted: Thu Jan 19, 2006 11:29 am    Post subject: 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.
DiGro
Super User

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

 Posted: Thu Jan 19, 2006 11:42 am    Post subject: 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)
asoler
General User

Joined: 19 Jan 2006
Posts: 8

 Posted: Thu Jan 19, 2006 11:48 am    Post subject: Ok, but en excel if you try find ' you could. Here i can`t find and replace.
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

 Posted: Fri Jan 20, 2006 1:38 am    Post subject: 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
asoler
General User

Joined: 19 Jan 2006
Posts: 8

 Posted: Fri Jan 20, 2006 2:39 am    Post subject: 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.
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

 Posted: Fri Jan 20, 2006 3:37 am    Post subject: yes you are right. you need to check out Regular Expressions in Find and replace_________________carl Using OpenOffice.org 2 on XP sp2
asoler
General User

Joined: 19 Jan 2006
Posts: 8

 Posted: Fri Jan 20, 2006 4:18 am    Post subject: I have probing all alternatives, with and without regular expressions, with hexa codes, with slash, etc....
JohnV

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

 Posted: Fri Jan 20, 2006 9:02 am    Post subject: 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.
asoler
General User

Joined: 19 Jan 2006
Posts: 8

 Posted: Fri Jan 20, 2006 9:30 am    Post subject: Not work
JohnV

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

 Posted: Fri Jan 20, 2006 12:38 pm    Post subject: 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.
NoOneLeft
General User

Joined: 20 Jan 2006
Posts: 13

 Posted: Fri Jan 20, 2006 7:10 pm    Post subject: 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 HoursGoto page 1, 2, 3  Next Page 1 of 3

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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