CODEWITHSUNDEEP
libreofficelibreoffice-calc
shuhalo
shuhalo

Reputation: 6482

Libreoffice Calc: convert many cells with text to numbers

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?

Upvotes: 24

Views: 17462

Answers (5)

Magnus
Magnus

Reputation: 31

Let's say cell A1 contains:   '14.05
in cell A3 enter the following:

=VALUE(REGEX(REGEX(A1; "\."; ","; "g"); "[^0-9,]"; ""; "g"))

What happens is...
  1:  REGEX(A1; "\."; ","; "g")  converts all instances of dot "." to comma ","
  2:  REGEX(xxx; "[^0-9,]"; ""; "g"))  removes anything not a number or a comma.
  3:  finally VALUE(xxx)  converts the resulting string to a number.
(the formula works from the inside out)

The drawback here is that this particular suggestion works for one cell only so it has to be copied for each additional cell, maybe to a new column?
I try to avoid messing with formatting if possible. Having the innards sanitized first then tend to cosmetics usually pays off ;)

Upvotes: 0

Jiří Kolafa
Jiří Kolafa

Reputation: 1

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

Upvotes: 0

Jeff
Jeff

Reputation: 11

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.

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61975

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.

Upvotes: 27

Jim K
Jim K

Reputation: 13819

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)

Upvotes: 7

Related Questions