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, it would be considered a number. For example, the 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 a number (!), it would be right aligned.
To convert the above list, use the 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, their internal value 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 the F1 cell and use the 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.
This way, you can quickly convert text to numbers.
Feel free to drop a comment using the comment box below for any questions.