Frequently asked questions - Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

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 having textual content, that is (later) formatted as number, 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. Note that only changing the number formats does not change the content; the value strings have to be re-entered to get converted, which the following methods do.

Find & Replace number text

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.

Find & Replace with different decimal separator

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.

Find & Replace wrong date order

If you pasted or imported dates in a different Month/Day/Year order, for example 06/28/2021, than your locale's that uses Day/Month/Year order, for example 28/06/2021, and/or some dates like 06/01/2021 were recognized but yielding a wrong date and others like 06/28/2021 weren't, then

do not press Ctrl+M and do not clear number/date formats on selected cells, leave all date formats intact but if there are cells formatted to Text then change those and only those to General
mark the cell range containing the data to convert
press Ctrl+H or choose Edit -> Find & Replace from the menu
- enter Find: (\d+)/(\d+)/(\d+)
- enter Replace: $2/$1/$3
- under Other options activate
  - Current selection only
  - Regular expressions
- hit Replace All

Explanation of the Find regular expression:

(\d+) a group of digits, month
/ the date separator
(\d+) a group of digits, day of month
/ the date separator
(\d+) a group of digits, year

Explanation of the Replace expression:

$2 the second group, group of digits, day of month
/ the date separator
$1 the first group, group of digits, month
/ the date separator
$3 the third group, group of digits, year

The text source and the target locale may have other requirements regarding order of day,month,year and date separator; then adapt.

To replace to the international YYYY-MM-DD ISO 8601 date format that is understood in every locale use (assuming the original Month/Day/Year order from above)

Replace: $3-$1-$2

or if the year was only two digits then prefix the century because otherwise the result would be ambiguous and not result in an ISO date, like

Replace: 20$3-$1-$2

Find & Replace wrong date order with month names

If you pasted or imported dates that contain month names, for example April 1, 2021 and your locale uses a different day/month order, for example 1 April 2021, or even different names in a different language, 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
- if your locale does not use the month names that are required for the text to be converted to date then temporarily apply a date format of a locale that uses the desired names; you can then later after the conversion apply the desired final date format of your (default) locale.
press Ctrl+H or choose Edit -> Find & Replace from the menu
- enter Find: ([\p{Letter}]+)\s+(\d+).*\s+(\d+)
- enter Replace: $2 $1 $3
- under Other options activate
  - Current selection only
  - Regular expressions
- hit Replace All

Explanation of the Find regular expression:

([\p{Letter}]+) a group of Unicode letter characters, month
\s+ at least one whitespace character
(\d+) a group of digits, day of month
.* any character, 0 or more, matches the comma in the example
\s+ at least one whitespace character
(\d+) a group of digits, year

Explanation of the Replace expression:

$2 the second group, group of digits, day of month
one space
$1 the first group, group of letters, month
one space
$3 the third group, group of digits, year

The target locale may have other requirements regarding order of day,month,year; then adapt.

CT2N - Convert Text To Number extension

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.

CSV Import

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).

Convert using Text to Columns

Using Data ▸ Text to Columns... is also an option:

Using Text to Columns function