Frequently asked questions - Calc
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
- 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 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.