Convert Text To Number (and date)

Provider:
Nou&Off
Maintainer:
cornouws
Rating:
4.076925
Average: 4.1 (13 votes)
Application:
Calc
Tags:
calc, , language tools, , spreadsheets, , formatting, , conversion, , numbers, , dates,
Screenshots:
http://www.nouenoff.nl/downloads/CT2N_1.1.0.png
Post date:
Saturday, 9 February, 2008 - 22:41
Statistics
Week: 110 - Month: 453 - Year: 5,839 - Timeline
Download extension
System Independent version - All releases
Compatible with OpenOffice 4: Unknown
User feedback:
Compatible with OpenOffice 4.x?

ConvertTextToNumber replaces numbers and dates, formatted as text, with real numbers.
Choices can be made about marking of cells, including cells with non- default decimal separators, conversion of dates, and more.

As a result of the conversion, the text cells will become real numbers, and then will be counted in formulas Calc.
Calc does not use numbers formatted as text. Other spreadsheet programs may well do that in certain circumstances.
This extension adds the tool bar 'CT2N' to Calc only. The screen shot shows it all.

Available languages: Spanish, Swedish, Russian, French, English, Italian and Dutch. Translations for other languages would be gladly accepted.

Pls note: We get requests about all kind of clean-up actions, that can be necessary when copying data from a website. These are not the original problem that this extension addresses. However, since it would make a good extra functionality, we kindly thank for this input and plan the ideas for a future version.

Convert Text To Number (and date)

Version Operating system Compatibility Release date
1.4.0 System Independent 3.4 27/09/2010 - 07:02 More information Download
1.3.2 System Independent 3.4 02/02/2009 - 18:19 More information Download
1.3.0 System Independent 3.4 25/01/2009 - 07:02 More information Download
1.2.0-Final System Independent 3.4 04/11/2008 - 11:55 More information Download
1.1.2 System Independent 3.4 21/08/2008 - 11:52 More information Download
1.1.1 System Independent 3.4 11/08/2008 - 16:42 More information Download
1.1.0 System Independent 3.4 07/08/2008 - 11:35 More information Download
1.0.3 System Independent 3.4 04/07/2008 - 08:03 More information Download
1.0.2 System Independent 3.4 03/07/2008 - 11:51 More information Download
1.0.1 System Independent 3.4 17/05/2008 - 18:03 More information Download
1.0.0 System Independent 3.4 16/05/2008 - 07:36 More information Download
0.9.2 System Independent 3.4 14/02/2008 - 11:28 More information Download
0.9.1 System Independent 3.4 10/02/2008 - 18:16 More information Download
0.9.0 System Independent -- 09/02/2008 - 23:01 More information Download

Comments

Hi!

I tried this extension on spreadsheet converted from Excel xls file with lot of this kind of numbers - '7,8. But nothing happened. Converter just select all content of the spreadsheet and prints message with empty list of edited cells. Ubuntu 7.10, openoffice.org 2.3.1. Am I doing something wrong?

Andis

Thanks for your comment, Andis. Was bug that is fixed now in 0.9.2
Cor

Hi, this is a great extension for OO. But is there any extension can use for column to text?

Thanks for the compliment.
reg. your question: is it this one from OOoMacro's:
http://www.ooomacros.org/user.php#104183 ?

Pls note that OOo 2.4.0 (RC2 is on it's way) will hold that function.
See the draft info:
http://wiki.services.openoffice.org/wiki/New_Features_2.4#Calc_2

Regards,
Cor

I have just downloaded your extension Convert Text to Numbers Version 1.0.1 , and, find it very useful. I am using it with OpenOffice 2.4.0, and Windows.

Three comments, while using it in Calc:

  1. Highlight Cells, colors -- when not checked ( unchecked ) . . . it still highlights ( Background Color ) the cells that are changed, even when NOT checked
  2. Icon on Toolbar - with mouse over the Icon . . . in English Version it displays "Start" . . . perhaps it should display "Convert Text to Number" . . . or . . . something like that.
  3. Would it be possible, in a future release, to also convert either / both a Date and / or Time from text to a number / value? For example, '2008-06-14 to a date, OR, '15:03 to a time?

Great job, please keep up the good work.

Sliderule

Hi Sliderule,

Thanks for your comments.
Reg. 1: the cells are selected, not marked with a color.
But the selection can be made undone (for a next version).
Reg. 2: Good tip. Takes some effort for localization however. So might take a while.
Reg. 3: Have had a similar request. Is somehow related to current functionality, so I plan it for a next version.

Regards,
Cor

Hi,
Your extension is very usefully, because in Excel to convert text to number is very easy and it is very necessary to be easy in openoffice also.
My problem was with numbers in format engineering as: 1.80022846907377E-02 for example. This kind of numbers excel know to convert, but sCalc don't. It will be very good if this problem will be resolved in the future versions of extension, and maybe this extension will be included in future version of openoffice. Excuse me for my English, and good luck.

Silviu Berbinschi

Hi,
Thank you so much for this extension !
Good luck to you

This worked great on my office pc but for some reason I get an 'Unknown Error' when using it on my laptop, any ideas? if it helps the laptop is a hp 6735s preloaded with Windows 7. Thanks

This worked great for me in converting large columns of dates imported as text to real dates for calc.

Works just as described!

Just purchased a new laptop with 64-bit Windows 7. Installed OpenOffice.org (new user) and was using Calc. I copied data from a website and pasted it in and had an issue with numbers coming in as text. Cell value shows just the number preceded with the single quote. This add-on seemed that it would be the perfect fix for me. Whether I select a single cell, a group of cells, or choose whole sheet, it indicates there is nothing to change.

Has this been verified for Windows 7 64-bit? Thanks!

Cor,
Thanks for the quick response. I sent the test data to what I believe was your correct email address. As you will see, it is nothing out of the ordinary, a column of numbers formatted as text that I want to convert to a number format.
'42
'27
'1
'33
'21
etc.

My understanding is that it should work similar to the "=Value(xx)" function, correct?

Great tool that works perfectly with any string containing just the unwanted ' and numerals. But it ignores data that was originally a percentage -- numerals followed by % -- converted to text during export by adding the unwanted ' and resulting in the string '12.3%. Can this be converted from text by the correct setting of CT2N parameters, or does the presence of the % in the string cause CT2N to always ignore it?

Hi KiwiLad
thanks for your feedback.
Indeed, % characters are seen as other text currently, so the cells are not handled.
I'll mark this one as a possible option for a next version.

The conversion from text to number works fine if the text version is '123.45. It doesn't work if the text version is ' 123.45. If one manually removes the spaces between the ' and the first digit, the conversion will work fine. It would be nice to get a fixed version as many of the files I import have the numbers as text, with spaces between the ' and the start of the number.

Cline

hi Cline,

Yep, thanks for that idea too. Will put it on the list for the next version!
Cheers

Works great on dates and numbers, however, my broker account down load is brought up in calc with both the apostrophe and dollar sign before the number ('$123.00), have I done something or is this one that the Convert Text To Number does not handle?

Hi,

That indeed is not implemented.
To integrate that option in a coherent manner, all kind of valuta signs should be checked. I'm not sure if I can easily find a handy way for that.
An option maybe could be giving a field where the user could enter a valuta sign.
Will think about this. But I guess you can work around this by first search/replace the $ with nothing and then ..

The download I got has all the "numbers" formatted like
'121.44
I used your extension but it just says "Nothing to change".
I have Windows 8 & OpenOffice.org 3.4.1.
A little help?