Convert Text to Number in LibreOffice Calc

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.

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 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.

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, 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.

SEE ALSO:  Basics of Relative, Absolute and Mixed Reference in Calc
Copy Step
Copy Step


Right click in F1 cell and use 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 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.


Support Us

Be an expert on LibreOffice.

Small and quality tutorials. Delivered. Thrice a week.

One thought on “Convert Text to Number in LibreOffice Calc

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Back to top