Home › Browse Extensions

Convert Text To Number (and date)

by Nou&Off
4
Average: 4 (12 votes)
TextToNumber_0.png

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.

Download extension
Operating System: System Independent
Version: 1.4.0
Date: 2010-Sep-27
Size: 44.69 KB

License: Opensource | GNU Lesser General Public License
Provider: Nou&Off
Further product information: Screenshots
Report abuse here
Downloads: Today: 14 | Week: 140 | Month: 610 | Year: 84,787

Comments

Tue, 11/15/2011 - 15:41 — KiwiLad

conversion of 'n.n%

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?

Tue, 05/10/2011 - 04:08 — Darth_Vukster

Working for Windows 7?

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!

Tue, 05/10/2011 - 05:35 — cornouws

Hi Darth, Thanks for

Hi Darth,
Thanks for writing...
Not sure in Win7 is the problem here. There seem to be some more circumstances, not initially intended for handling, that people stumble upon now and then. I try to gather them ... and then at a good moment make some time (sponsors?) to try to include it. Prize will be the performance I am afraid. But we'll see.
For now: could you pls mail me a sample of the data that has the problem? Thanks,
Cor

Thu, 05/19/2011 - 00:42 — Darth_Vukster

Sent

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?

Sun, 05/01/2011 - 07:22 — arifin13579

Great!

Works just as described!

Tue, 05/10/2011 - 05:31 — cornouws

Thanks for your comment

Thanks for your comment :-)
Cor

Sat, 02/26/2011 - 15:12 — lloyd_hayes

Worked great

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

Tue, 09/07/2010 - 14:32 — dustywarrior

This worked great on my

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

Tue, 09/07/2010 - 14:37 — cornouws

an 'Unknown Error'

Hi *,
Sorry, I have no idea about unknown errors caused by propably the OS or the configuration. Maybe a skillend person on that area can help you out.
Best - Cor

Sat, 03/27/2010 - 18:31 — elitogo

Hi, Thank you so much for

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

Tue, 02/16/2010 - 09:19 — sberbinschi

Sugestion

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

Sat, 06/14/2008 - 01:39 — sliderule

Version 1.0.1 -- Highlight Cells

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

Thu, 07/03/2008 - 21:40 — cornouws

1.0.1 -- Highlight Cells

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

Thu, 02/21/2008 - 03:57 — sam5588

Text to Column & Column to text

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

Fri, 02/29/2008 - 07:10 — cornouws

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

Thu, 02/14/2008 - 11:33 — andisl

Nothing happens

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

Thu, 02/14/2008 - 16:31 — cornouws

Nothing happens

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