Converting a Text String to a Number in Calc

From NeoWiki

In a Calc document, sometimes numbers are entered in text format, for example when the tables come from converted files (such as converted .pdf files). In those cells, numbers appear normally, but are aligned to the left like character strings. If you click in a cell which contains a number, for instance 23, you will see '23 in the formula bar. In order to convert those text strings to number format, you have two possibilities

1. Use the Find & Replace function
2. Use the VALUE function.

Using the Find & Replace Function

• Select the cell range which contains text strings.
• Choose the Edit menu then the Find & Replace… submenu.
• In the Find & Replace window, enter "^[0-9\-]" (without quotes) in the Search for field.
• In the Replace with field, enter "&" (without quotes).
• Click Other options or More options button.
• Check the Regular expressions checkbox.
• Set the Search in listbox to Values
• Click Replace All.

Using the VALUE Function

Let us assume that the cells A1, A2, A3 contain 23, 12, 36 in text format. We are going to display the same values in the cells B1, B2, B3, but in number format.

• Click in the cell B1
• Click on the "=" sign on the left side of the formula bar.
• type VALUE in the input line, then press Enter or Return.
  • You can also enter that function by clicking on the formula icon on the left side of the Σ sign. In the Function Wizard dialogue that opens, click on the Functions tab, then the Text category and select VALUE.
  • Click Next.
• Click in the cell A1. Now it is highlighted with red borders.
• Press Enter or Return. The number 23 is aligned to the right in the cell B1.
• Then select the three cells B1 to B3, by clicking in the cell B1 then dragging the cursor to the cell B3, or by clicking in the cell B1 then holding the Shift key down while clicking in the cell B3.
• Go to the Edit menu, then the Fill submenu and select Down.
  

The three cells B1, B2, B3 now contain 23, 12 and 36 in number format.

Removing the cells which contain the text strings

Now, you may want to delete the cells A1 to A3. Unfortunately, if you delete them the cells B1, B2 and B3 will contain only 0, since it is the value that the VALUE function assigns to an empty string.
In order to solve that problem, you can simply hide the column A by right-clicking (or Ctrl-clicking) on the column header then selecting Hide.
Another method, more complex, will allow you to really delete it.

1. Export the sheet in Text CSV format:
   • In the File menu choose Save As….
   • In the Save window and in the File type drop down menu, choose Text CSV (.csv) and check Edit filter settings.
   • Click Save.
   • In the dialogue asking if you want to save the document in the .csv format, click Yes.
   • In the Export of text files dialogue, set the Field delimiter option to {Tab} .
   • Click OK.
2. Import the .csv file in a spreadsheet and remove the column A:
   • Open a Calc document (which can be the same as the one from which you exported a sheet in .csv format).
   • Go to the Insert menu and select the Sheet From File… submenu.
   • Choose the .csv file you created before.
   • In the Text Import dialogue, be sure that the Tab option is selected in the Separator options section.
   • Click OK.
     

A new sheet is added in which you can delete one of the two columns A or B. Now, both of them contain numbers.