Converting a Text String to a Number in Calc

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 15:31, 23 March 2007 (edit)
Jgd (Talk | contribs)
(add Image)
← Previous diff
Current revision (00:59, 21 August 2017) (edit) (undo)
( | contribs)
(Revert most of last change as the change solves a different issue than the original steps)
 
(11 intermediate revisions not shown.)
Line 1: Line 1:
{{Fixme}} {{Fixme}}
-In a Calc document, sometimes it happens that some cells formatted in ''Text'' format contain ''Numbers'', for example when the tables come from converted files, (such as converted .pdf files).+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, the numbers appear normally, but they are aligned to the left like strings of characters. If you click in a cell which contains such a number, for instance 23, you will see '23 in the formula bar.+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 ''texts'' to ''numbers'', you have a function available: the '''VALUE''' function.+In order to convert those text strings to ''number'' format, you have two possibilities
 +# Use the '''Find & Replace''' function
 +# Use the '''VALUE''' function.
-==Converting a ''text'' to a ''number''==+==Using the Find & Replace Function==
-Let us assume that the cells A1, A2, A3 contain the ''texts'' 23, 12, 36. We are going to display the same values in the cells B1, B2, B3, but in ''number'' format.+*Select the cell range which contains text strings.
 +*Choose the {{menu|Edit}} menu then the {{menu|Find & Replace…}} submenu.
 +*In the {{window|Find & Replace}} window, enter "'''^[0-9\-]'''" (without quotes) in the {{section|Search for}} field.
 +*In the {{section|Replace with}} field, enter "'''&'''" (without quotes).
 +*Click {{button|Other options}} or {{button|More options}} button.
 +*Check the {{prefName|Regular expressions}} checkbox.
 +*Set the {{prefName|Search in}} listbox to {{button|Values}}
 +*Click {{button|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 in the cell B1
-* Click the minus sign "'''='''" on the left side of the formula bar.+* Click on the "'''='''" sign on the left side of the formula bar.
-* type '''VALUE''' in the input line. +* type '''VALUE''' in the input line, then press {{key|Enter}} or {{key|Return}}.
-** You can find that function by clicking the formula icon on the left side of the Σ sign. In the {{window|Function Wizard}} dialogue that opens, click the {{section|Functions}} tab, then the {{prefName|Text}} category and select '''VALUE'''.+** You can also enter that function by clicking on the formula icon on the left side of the Σ sign. In the {{window|Function Wizard}} dialogue that opens, click on the {{section|Functions}} tab, then the {{prefName|Text}} category and select '''VALUE'''.
** Click {{button|Next}}. ** Click {{button|Next}}.
-* Click in the cell A1. Now it is bordered with a red rectangle. +* Click in the cell A1. Now it is highlighted with red <!--isn't it blue?--> borders.
-* Press {{key|Enter}} or {{key|Return}}. The number 23 appears aligned to the right in the cell B1.+* Press {{key|Enter}} or {{key|Return}}. The number 23 is aligned to the right in the cell B1.
-* Then select the three cells B1 to B3, by clicking the cell B1 then dragging the cursor to the cell B3, or by clicking the cell B1 then, pressing the {{key|Shift}} key, clicking the cell B3.+* 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 {{key|Shift}} key down while clicking in the cell B3.
-* Go to the {{menu|Edit}} menu, then the {{menu|Fill}} sub-menu and select {{menu|Down}}.<br/>+* Go to the {{menu|Edit}} menu, then the {{menu|Fill}} submenu and select {{menu|Down}}.<br/>
-The three cells B1, B2, B3 now contain the ''numbers'' 23, 12 and 36.<br/>+The three cells B1, B2, B3 now contain 23, 12 and 36 in ''number'' format.<br/>
[[Image:Number.png|Converting a Text String to a Number]] [[Image:Number.png|Converting a Text String to a Number]]
-==Removing the cells which contain ''texts''==+==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.<br/> 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.<br/>
-In order to solve that ptoblem, you can simply hide the column A by right clicking (or Ctrl-click) the header of the column then selecting {{prefName|Hide}}. <br/>+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 {{prefName|Hide}}. <br/>
-Another method, more complex, allows you to really delete it.+Another method, more complex, will allow you to really delete it.
-# Export the sheet in <tt>Texte CSV</tt> format:+# Export the sheet in <tt>Text CSV</tt> format:
#* In the {{menu|File}} menu choose {{menu|Save As…}}. #* In the {{menu|File}} menu choose {{menu|Save As…}}.
-#* In the {{window|Save}} window and in the {{section|File type}} drop down menu, choose {{prefName|Text CSV(.csv)}} and check {{prefName|Edit filter settings}}. +#* In the {{window|Save}} window and in the {{section|File type}} drop down menu, choose {{prefName|Text CSV (.csv)}} and check {{prefName|Edit filter settings}}.
#* Click {{button|Save}}. #* Click {{button|Save}}.
#* In the dialogue asking if you want to save the document in the <tt>.csv</tt> format, click {{button|Yes}}. #* In the dialogue asking if you want to save the document in the <tt>.csv</tt> format, click {{button|Yes}}.
Line 30: Line 42:
# Import the <tt>.csv</tt> file in a spreadsheet and remove the column A: # Import the <tt>.csv</tt> 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 <tt>.csv</tt> format). #* Open a Calc document (which can be the same as the one from which you exported a sheet in <tt>.csv</tt> format).
-#*Go to the {{menu|Insert}} menu and select the {{menu|Sheet From File…}} sub-menu.+#*Go to the {{menu|Insert}} menu and select the {{menu|Sheet From File…}} submenu.
#* Choose the <tt>.csv</tt> file you created before. #* Choose the <tt>.csv</tt> file you created before.
#* In the {{window|Text Import}} dialogue, be sure that the {{prefName|Tab}} option is selected in the {{section|Separator options}} section. #* In the {{window|Text Import}} dialogue, be sure that the {{prefName|Tab}} option is selected in the {{section|Separator options}} section.
Line 36: Line 48:
A new sheet is added in which you can delete one of the two columns A or B. Now, both of them contain ''numbers''. A new sheet is added in which you can delete one of the two columns A or B. Now, both of them contain ''numbers''.
-{{botlangbarEN|[[Fr:Convertir une Chaîne de Texte en Nombre dans Calc|Français]]}}+{{botlangbarEN|[[Fr:Convertir une Chaîne de Texte en Nombre dans Calc|Français]] [[It:Convertire_una_stringa_di_testo_in_un_numero_in_Calc|Italiano]]}}
[[Category:Tips and Hints]][[Category:NeoOffice]] [[Category:Tips and Hints]][[Category:NeoOffice]]

Current revision

This article is not up to date, or it needs structural or stylistic changes to conform with the rest of this wiki.

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.
Converting a Text String to a Number

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.


This article in other languages: Français Italiano
Personal tools