Frequently asked questions - Calc

From The Document Foundation Wiki
< Faq‎ | Calc
Jump to: navigation, search
Other languages:
dansk • ‎English


How to convert number text to numeric data

It can happen that data pasted from the clipboard or imported from CSV or entered into a cell that was already formatted as Text is of type Text although it visually looks like a number or date, and text can't be used in calculations. The cell content in this case in the Input Line is preceded with a leading ' apostrophe which is not part of the cell content, to indicate that the content could be interpreted as number if it wasn't text. As a consequence, arithmetic calculations involving such cells may yield a #VALUE! error, or the cell is ignored in functions that take a number sequence of a range, for example SUM(). To convert such number text to numeric data do the following, which assumes that the text actually can be converted, i.e. uses the separators (decimal, date, ...) of your locale.

  • mark the cell range containing the data to convert
  • press Ctrl+M or right click on the marked range to get the context menu and select Clear Direct Formatting
    • if your locale does not use the separator(s) that are required for the text to be converted to number then temporarily apply a number format of a locale that uses the desired separator(s), for example English-US to get the . dot decimal separator; you can then later after the conversion apply the desired final number format of your locale. Either that, or replace the decimal separator, skip to the next example further below.
  • press Ctrl+H or choose Edit -> Find & Replace from the menu
    • enter Find: .+
    • enter Replace: $0
    • under Other options activate
      • Current selection only
      • Regular expressions
    • hit Replace All

This enters the cell content anew, basically as if it was entered from keyboard, and detects numeric/date/time input.

If the number wasn't recognized because your locale uses a different decimal separator than the data, for example , comma instead of data's . dot (and thus the Input Line does not display the ' apostrophe prefix), then

  • mark the cell range containing the data to convert
  • press Ctrl+M or right click on the marked range to get the context menu and select Clear Direct Formatting
  • press Ctrl+H or choose Edit -> Find & Replace from the menu
    • enter Find: .
    • enter Replace: ,
    • under Other options
      • activate Current selection only
      • deactivate Regular expressions
    • hit Replace All

Note this does of course not work if the numbers contain a mix of group separators and decimal separators and the replacement would yield identical separators.

If you need such functionality more often or to convert data with separators differing from your locale, install the CT2N - Convert Text To Number (and dates) extension.

If your data resulted from a CSV file import, you may not have noticed that in the import dialog you can select the import locale to match the locale of the data. This helps to recognize decimal and group separators, currency symbols and day and month names in dates to yield proper numeric data. Also, some dates and currency formats are only recognized if the option Detect special numbers is activated (which then again might convert some text to numeric where conversion is not wanted, hence the option).