17

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?

shuhalo
  • 5,732
  • 12
  • 43
  • 60
  • 1
    Mass conversion possible, which solutions did you try when you searched the Internet for "calc text to number conversion"? – clarity123 Mar 15 '18 at 14:48
  • 1
    I googled and did not find anything. The closest thing I found relates to OpenOffice. https://wiki.openoffice.org/wiki/Transforming_text-formatted_numbers_into_numbers This, however, doesn't change anything at all. – shuhalo Mar 15 '18 at 18:02
  • When you search for "calc text to number conversion" try the neooffice solutions, its ok that it isn't strictly named LibreOffice , because many of these Open Source projects are forks of OpenOffice anyway so worth a try – clarity123 Mar 15 '18 at 18:07
  • Also the OpenOffice solution you tried, works for me and I am using LibreOffice 5.2.7.2 . What version are you using, maybe it is a version issue, go to Help, About LibreOffice, what version does it say? – clarity123 Mar 15 '18 at 18:17
  • 2
    You need to use Find and Replace to remove the `'`. https://ask.libreoffice.org/en/question/1843/changing-text-to-numbers/?answer=68235#post-id-68235. Related: https://stackoverflow.com/questions/16125504/libreoffice-calc-how-to-convert-unformatted-text-to-numbers-or-other-format – Jim K Mar 15 '18 at 19:04
  • stumbling across the same issue. flabbergasting how bad LO is. – axd Jul 18 '21 at 00:03

4 Answers4

23

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

enter image description here

(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:

enter image description here

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.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • 1
    doesn't work... a cell containing `18.9` is never changed – axd Jul 18 '21 at 00:07
  • @axd: It works. Maybe your `Calc` uses different locale settings where comma ist set as decimal separator. Then `18.9` will never treated as numeric. Then only `18,9` will be treated as numeric. – Axel Richter Jul 18 '21 at 04:46
  • @axd in that case, you need to click in the dialog on the column header and select "US English" – jpalecek Aug 14 '21 at 12:14
  • This answer doesn't work for multiple columns. – J. Schmidt Nov 03 '21 at 10:02
  • This works, though probably not for multiple columns, as pointed out by J. Schmidt. But the Search/Replace trick with regular expression matching linked to by Jim K is more flexible and will work with any number of columns. Just search for ".*" and replace with "&", with the regular expression checkbox checked. – Otto G Apr 03 '22 at 22:15
3

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)

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • Note to @skierpage who proposed an edit: It's true that the original answer was misleading, but it would have been better to add a comment instead. – Jim K Apr 15 '19 at 12:29
0

Select the cells. Right click / format cells

Note what the format is currently selected to - not what you expected, I'm sure

Then, choose the Number format you wish / enter

Select the cells once again

Menu / Data / Text to Columns

Be sure to select Trim spaces

/ Enter

That should do it.

Jeff
  • 1
0

"Text to Columns" works for one column only, not an array, so it may be tedious.

My problem was in importing a text (e.g., CSV) file with numbers. Opening a CSV-file (e.g., TAB-separated) in Libre Calc makes text-formatted numbers ('3.14). Pasting the same information to an empty ods-file converts the numbers to type Number/General.

So, to convert a rectangle with 'numbers to numbers, mark your 'data, copy to a file (linux: cat > file.txt), open it in a suitable editor not destroying TABs, copy (or use xclip < file.txt if you have xclip installed), than paste to Libre Calc. An import dialog will appear - select TABs. You should have the decimal separator consistent with your locale.

Jiří Kolafa
  • 1