Convert Text to Number in LibreOffice Calc


This tutorial will 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, they would be considered numbers. For example, the string representation of a number can be entered as ‘123 as below.

String of Numbers
String of Numbers

If a number is represented as text/string, it would be shown as left aligned as shown above. If a number is represented as a number, it would be right aligned.

To convert the above list, use the VALUE function. This function takes the string and returns the numbers as below.

Convert Text to Numbers
Convert Text to Numbers

Enter the formula as =VALUE(A1) in cell D1 and drag the cell’s right bottom plus sign handle till D7.

Drag Cell with Formula
Drag Cell with Formula

As you can see, the values are converted to numbers using the function.

Numbers Converted from String
Numbers Converted from String

Though the texts are now numbers, their internal value remains the formula. To remove the formula and get the actual converted numbers, use the paste special feature as below.

Select the entire range and right-click -> Copy.

Copy Step
Copy Step

Right-click in the F1 cell and use the option: Paste Special -> Paste Special. Then, use the option button Values Only at the top.

Paste Special Values
Paste Special Values

Now, you can see the actual numbers instead of the formula.

Paste Special - Values [After]
Paste Special – Values [After]

This way, you can quickly convert text to numbers.

Feel free to comment using the comment box below for any questions.

Join our Telegram channel and stay informed on the move.

Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Newest Most Voted
Inline Feedbacks
View all comments

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.

Ian Allen

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.


This workaround does not work now Version:
Build ID: 1:6.4.7-0ubuntu0.20.04.4
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3;
Locale: en-ZA (en_ZA.UTF-8); UI-Language: en-ZA
Calc: threaded

Oberon Robinson

To convert strings to numbers in existing cells, select the cells and use “Data -> Text to columns”.

N. Lampert

I got this to work: Text to Numbers:

  1. Right click mouse and drag down the cursor (grab) all Columns and Rows to highlight the txt you want to convert to Currency (my case is currency but could be numbers or other numeric choice).
  2. Left click the mouse and select Format Cells.
  3. Select Currency (or whatever on the dropdown menu you want to convert to), then click OK.
  4. Now I could easily convert 1 column at a time and it was simple to do.
  5. Go to one column of currency (numbers, and right now, the cells are still text). Right click mouse and drag down the cursor (to grab) all cells in one column to highlight the text.
  6. Go up to the menu and select Data, then select Text to Columns, then check the boxes for Fixed width and Evaluate formulas. Now click OK.

You should have currency (in my case) or numbers or whatever numeric choices you chose.

Version Information
Vers. (x64)
Build: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
Locale: en-US


Does not work in Version:
Build ID: 1:6.4.7-0ubuntu0.20.04.4
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3;
Locale: en-ZA (en_ZA.UTF-8); UI-Language: en-ZA
Calc: threaded


THIS IS THE WAY !!!!! Select the all the cells with numbers in the column
go to the tab “Data” >> “Text to columns” and ready. do this for all the 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).


No, this causes the spreadsheet to sort the column.


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?


I get “Err: 502” and have tried all the other solutions and they don’t work.
Build ID: 1:6.4.7-0ubuntu0.20.04.4
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3;
Locale: en-ZA (en_ZA.UTF-8); UI-Language: en-ZA
Calc: threaded


ok, clumsy but it works like this:


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.




Did not work for me. Leads to inscrutable “Err:502” message.


I found out that because my locale is South Africa, Libre Office insists that the decimal is indicated by a comma “,” not a point “.”. NB. This is a subtle difference.
Recognising user locale’s is a new feature which was ignored by software developers for the last 40 years. Consequently, banks etc. did and still produce statements with decimal points in the numeric data, for backward compatibility with legacy software. Unfortunately, the spreadsheet software does not recognise this and the atrocious error message does not provide any clues to the cause. Which it could so easily!


In SA our decimal is supposed to be a comma, but out locale has been set to USA for ever, where the decimal is a point. Bank statements etc come with points. If the data pasted contains points, and the locale is SA, then this problem occurs. I just find and replace with points with commas and off we go.


Kindly inform as to how I can convert number to text in libre calc?