This tutorial would explain on how to convert a list of “text which represents numbers” to numbers.
Convert Text to Number
In Calc, if you enter numbers with an apostrophe at the beginning, it would be considered as number. For example, string representation of a number can be entered as ‘123 as below.
If a number is represented as text/string, it would be shown as left aligned as shown above. If a number is represented as number (!), it would be right aligned.
To convert, the above list, use VALUE function. This function, takes the string and returns as numbers as below.
Enter the formula as =VALUE(A1) in cell D1 and drag the cell’s right bottom plus sign handle till D7.
As you can see, the values are converted to numbers using the function.
Though the texts are now numbers, but their internal value still remains the formula. To remove the formula and get the actual converted numbers, use paste special feature as below.
Select the entire range and right-click -> Copy.
Right click in F1 cell and use option: Paste Special -> Paste Special. Then use the option button Values Only at the top.
Now you can see the actual numbers instead of the formula.
![Paste Special - Values [After]](https://libreofficehelp.com/wp-content/uploads/2018/03/Paste-Special-Values-After.png)
This way you can quickly convert text to numbers.
Feel free to drop a comment using comment box below for any questions.
Looking for something else?
Read our complete tutorial index of Calc, Writer, Impress and Draw.
How I can transfer english number notation with point as decimal parts into polish number notation with coma as decimal parts. In my Libre Office number format is 12345678,12345678. But in my PowerBasic big data format is 12345678.12345678. So In my LibreOffice my data for calculation are left assigned with point inside and I need mannually replaced each point on coma.
This is an extremely convoluted way to have to perform an everyday task, and LO really needs this functionality built-in.
It’s also not the easiest or fastest way to do this using existing LO functionality.
Agreed! I can’t believe this isn’t a one-click built-in operation in LO (to convert text into numbers).
I am afraid there is no one-click solution. In my case I exported an file created in MS Excel to ODS. The column with the numbers I saw were in fact text cells. Simple formatting didn’t do the job in LibreOffice. When I managed to change the text fields into numbers (starting with an apostrophe) the autosum function did not work. And to complicate matters, the operation Add (+ plus sign) did work!
I literally had to copy all the cells to a new column and use the paste special work around to over ride the data back into the original column.
To convert strings to numbers in existing cells, select the cells and use “Data -> Text to columns”.
This comment should replace the entire blog article above. This is the real solution, even if it is still a column by column hack. I would add that you can even make it quicker by:
1) select the column
2) Type “Alt” + d, Type “Alt” + x, and then hit Enter.
The whole column is now number formated correctly (text is not altered).
well, VALUE might be nice on fields containing numbers (in text format). but how do i convert any text to numbers? i mean, if it can be converted, then do it and if not (because it’s not a number) i’d like to have a default (like: 0). instead, with VALUE i get a lot of #VALUE! errors. i would like to avoid this and have neat numbers, even for fields not being able to convert. how to do that?
ok, clumsy but it works like this:
=IF(ISERROR(VALUE(N14));0;VALUE(N14))
Yes correct. You can reformat any #VALUE, #N/A error using IFERROR.
2022 and “calc” software still cant recognize that a single integer is actually a number and you still have to make 10 manipulations before it actually works.
What a joke
They should make it recognize what a number looks like before even making any new functionnality or correction.
Putting cell format as “Number” should AUTOMATICALLY convert existing values to number where possible.
Stupid.
Did not work for me. Leads to inscrutable “Err:502” message.