Libreoffice Calc: convert many cells with text to numbers

Question

I have got many cells in my file whose content is of the form

'14.05

I want to mass convert these cells, which contain text, to numbers, which I can compute with.

14.05

Is there a build-in function in Libre Office for such mass conversion, or do I need to fumble around with a macro?

Answer 1

If that apostrophe is a quote prefix rather than really cell content like so:

(note the apostrophe is only shown in formula bar but not in the cell)

then the following will be possible:

Select the whole column A. Then select Data - Text to columns from the menu bar:

Then click OK. Now all the content which looks like numbers will be converted to numeric.

The above works when dot is set as the decimal separator in your Calc. If you are using different locale settings where comma ist set as decimal separator, then 14.05 will never treated as numeric. Then only 14,05 will be treated as numeric.

Answer 2

Use Find and Replace to edit the data again, as explained at https://ask.libreoffice.org/en/question/1843/changing-text-to-numbers/?answer=68235#post-id-68235.

Put .* into 'Search For:' and & into 'Replace With:'

Then format the cells as numbers.

Related: LibreOffice Calc: How to convert unformatted text to numbers (or other format)