Converting text to numbers

users
#1

Hi ,

I have an auto generated xls file, and I have a column in it, which is defined as text.

I have numbers in that column, with trailing spaces which look like ;
"1.2 "
"12.9 "
"3.4 "

When I right click to the column, choose "Format Cells" and "Number", it doesn't convert. I can't use the values in any formula. How can I convert them to numbers? Is there a "quick convert" feature?

Excel can use those values in formulas, even if I don't convert and with trailing spaces..

Thank you

#2

Hi Arda,

I have numbers in that column, with trailing spaces which look like ;
"1.2 "
"12.9 "
"3.4 "

When I right click to the column, choose "Format Cells" and
"Number", it doesn't convert.

Yes. The type of your data is text. If you change the format, the
data type is still text.

Try the VALUE() function in order to convert text into numbers.

Excel can use those values in formulas, even if I don't convert and
with trailing spaces..

Excel does an implicit conversion of data type, which can be an
advantage or a disandvantage, depending on the point of view.

Stefan

#3

Hi Arda,

Arda Tunccekic wrote (07-12-10 09:31)

I have an auto generated xls file, and I have a column in it, which is
defined as text.

I have numbers in that column, with trailing spaces which look like ;
"1.2 "
"12.9 "
"3.4 "

When I right click to the column, choose "Format Cells" and "Number", it
doesn't convert. I can't use the values in any formula. How can I
convert them to numbers? Is there a "quick convert" feature?

The extension CT2N does help in many cases.
You may want to try it:
   http://extensions.services.openoffice.org/project/CT2N

(And if it does not work, pls let me know. Sometimes I make some changes, thus your situation might be covered by a future version.)

Regards,
Cor

#4

Hi Cor,

I think it did not work.

You can try it yourself.

- Open up a new spreadsheet. Choose Column A, right click on it and click on Format Cells. Make them Text.
- Now type "1.22 " , "2.4 ", "3.51 " in the first 3 rows.
- Go to Column B, select the first cell, click on the sum function symbol and choose the values on column A.

It doesn't calculate..
Nothing changes when I use the "Convert text to number" extension.

Maybe the extension can first trim the trailing spaces, and then change the cell formatting to number, if it can be done..

Thank you

#5

Hi Arda,

Arda Tunccekic wrote (07-12-10 13:27)

I think it did not work.

You can try it yourself.

- Open up a new spreadsheet. Choose Column A, right click on it and
click on Format Cells. Make them Text.
- Now type "1.22 " , "2.4 ", "3.51 " in the first 3 rows.
- Go to Column B, select the first cell, click on the sum function
symbol and choose the values on column A.

It doesn't calculate..
Nothing changes when I use the "Convert text to number" extension.

Thanks for your fast feedback. Sorry it doesn't help now.

Maybe the extension can first trim the trailing spaces, and then change
the cell formatting to number, if it can be done..

OK, I'll put it on the todo list, see if it can be changed.
Maybe I have some time next weeks to work on it.

Regards,
Cor

#6

Arda Tunccekic wrote (07-12-10 13:27)

- Now type "1.22 " , "2.4 ", "3.51 " in the first 3 rows.

With or whithout the " ?

Using Find & Replace, Regular expression checked
Without the "
Search : .*
Replace : &

With the "
Search : "(.*)"
Replace : $1

Gérard

#7

Hi Gérard,

My example is with spaces.. They are numbers like "3.51 "

Using Find&Replace, with regular expressions checked , searching for *[:space:]?$* , replacing with nothing removes spaces.

But this is can be a hard step for regular users. And again, they say me "ms excel does it, what is this now" :slight_smile:

#8

Message du 07/12/10 14:15
De : "Arda Tunccekic"
A : users@libreoffice.org
Copie à :
Objet : Re: [libreoffice-users] Converting text to numbers

Hi Gérard,

My example is with spaces.. They are numbers like "3.51 "

I saw this.
Spaces are removed by the way i told you.

No need to search them.

Using Find&Replace, with regular expressions checked , searching for
*[:space:]?$* , replacing with nothing removes spaces.

But this is can be a hard step for regular users. And again, they say me
"ms excel does it, what is this now" :slight_smile:

I don't use MS Excel...

#9

This is not right... not in all cases. My test with Excel 2000:
* Column A -> Cell format = Text
* A1 = "1,25 "
* A2 = "3,46 "
* A3 = "0,3 "
* B1 =SUM(A1:A3)

the result in cell B1 is 0 (zero) not the expected sum. Maybe Office XP and later version works better...

#10

Yes, I'm sorry, there is one more step in MS Excel.

Select A1, A2 and A3 and use the small box next to them (Convert to number tool)

Then the sum of the values give the real value.

It's easier in Ms Excel (That's why they wrote that small conversion toolbox, appearing next to the cell)

#11

The extension does work.

The problem is that there is a BUG in the Trim functions when handling
numbers.

If you do =TRIM(" Testing Trim ") and copy and Paste Special as Text to
another cell, you get "Testing Trim" (without quotes)

However if you do =TRIM("1.25 ") you get 1.25 as expected but not if you
do =TRIM(1.25 ), which is the same as doing =TRIM(A1). In this case the
spaces are not trimmed so you get "1.25 " (without quotes) again...

So apparently TRIM is not adding the quotes as expected :slight_smile:

Fixing this probably allows to use the Extension and make your users happy
:slight_smile:

#12

Imho the easiest way to convert text to numbers or numbers to text is to use Data -> Text to Columns.

You select you column (click the column header)
In menu Data -> Text to Columns
At the bottom of the wizard window, you select your column and change type to "Us English" .... And that's all folks !

Usually, this function is used to split one column to many ... but with only one colum it's really useful to make "inplace" type conversion

#13

Yes, that's a solution, thank you Sndl :slight_smile:

In this case, the values are;

#14

Stefan Weigel wrote:

#15

Stefan Weigel wrote:

Hi Arda,

I have numbers in that column, with trailing spaces which look like ;
"1.2 "
"12.9 "
"3.4 "

When I right click to the column, choose "Format Cells" and
"Number", it doesn't convert.

Yes. The type of your data is text. If you change the format, the
data type is still text.

Try the VALUE() function in order to convert text into numbers.

This only works if you remove the spaces first
BTW. Is there anywhere where I can find info on the Error Codes such as
511, 502 and 522?

Excel can use those values in formulas, even if I don't convert and
with trailing spaces..

Excel does an implicit conversion of data type, which can be an
advantage or a disandvantage, depending on the point of view.

Stefan

--
LibreOffice - Die Freiheit nehm' ich mir!

--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
List archive: http://www.libreoffice.org/lists/users/
*** All posts to this list are publicly archived for eternity ***

Tink.

#16

Hi,

Stefan Weigel wrote:

[...]

Excel does an implicit conversion of data type, which can be an
advantage or a disandvantage, depending on the point of view.

Stefan
Value(O) does not work until you remove the spaces.
BTW Where can I find the meaning of the Error Codes?
In this case it was Error 522

Err 522 means, that you have a circular reference.

There is a chapter of the OOo Calc guide about the error codes. You
can find it here:
http://www.oooauthors.org/english/userguide3/calc3/cg3-published

Sigrid

#17

Yes, that's a solution, thank you Sndl :slight_smile:

In this case, the values are;
------------------------------------------
A1(text) : "1.2 "
A2(text) : "2.5 "
B1(number) : =SUM(A1:A2) (it shows 0 now, it can't calculate..)
------------------------------------------

The operation is (as Sndl described)
------------------------------------------
1. Choose Column A
2. Chose Data-> Text to Columns from the top menu

In the wizard form;
3. Select the Column
4. Select "US English" from the Column type Combo
5. Click OK
------------------------------------------

B1 now shows the sum of the values (3.7 in this case)

Data type of A1 and A2 are still text, but that's not important for me
when the functions are working :slight_smile:

I think A1 and A2 aren't text, they are converted as numbers (right align in cell isn't it ?), but the column has text format.

When you change cell format, there is no conversion between text <-> number for already filled cells. But if you enter data in an empty cell (or replace existing datas), it will have the new format.

On the other hand, if you use de "Text to Columns" function, you can convert data type, but the cell format is left unchanged. So, further input in cells will have the cell format previously defined, not the data type used during the "Text to columns" conversion.