CALC convert text to numbers

users
#1

I have a spreadsheet that is the output of a SQLite Database on Android. It contains numbers that have been formatted as text. When I copy the columns into my LibreOffice Spreadsheet I want to make sure that they are interpreted as numbers.

However they come in as text and the manuals and help at Libre Office are less than useful.

https://help.libreoffice.org/Calc/Converting_Text_to_Numbers implies that this conversion happens automatically but it sure doesn't

I found reference to a value function, but no information on how to apply it to this data.

I tried just changing the format of the cells to be number but that didn't do anything at all.

I've also attempted various styles of paste special but still it doesn't work.

There has got to be a way to quickly say this text is all really numbers and get it working!

Please help

Eugenie (Oogie) McGuire
Desert Weyr http://www.desertweyr.com/
Paonia, CO USA

#2

http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

That extension should do the trick - we're trying to incorporate it into the core code but so far you need the extension.

All the best,
Joel

#3

I have a spreadsheet that is the output of a SQLite Database on Android. It contains numbers that have been formatted as text. When I copy the columns into my LibreOffice Spreadsheet I want to make sure that they are interpreted as numbers.

However they come in as text and the manuals and help at Libre Office are less than useful.

https://help.libreoffice.org/Calc/Converting_Text_to_Numbers implies that this conversion happens automatically but it sure doesn't

I found reference to a value function, but no information on how to apply it to this data.

I tried just changing the format of the cells to be number but that didn't do anything at all.

I've also attempted various styles of paste special but still it doesn't work.

There has got to be a way to quickly say this text is all really numbers and get it working!

Please help

If the numbers (formatted as text) are already saved in your
spreadsheet, I would normally suggest a simple procedure involving
"paste special" and multiply (by "1"). This has worked for me in
several other spreadsheet tools.

But Calc will (currently?) fail, because Calc adds a single quotation
mark at the beginning of the cell. So what seems to be just "1"
(without the double quotation marks), in Calc actually is "'1"
(without the double quotation marks, but including the single initial
single quotation mark). If you currently don't see the initial single
quotation mark, you could see it (in the formula bar) by copying one
of these cells and pasting it in a new one (among other options).

This single quotation mark will even remain after changing the format
from 'text' to 'numbers', so this is what makes the solution less
than simple.

You could select the relevant cells, change their format and then
'find and replace' on that same selection. But, since this is a
special (hidden) character, I'm not sure how to make it happen ('find
and replace' might not find the specific character).

As a simple user, I see this "hidden" addition of the initial single
quotation mark as a _BUG_, and as one of those basic "features" that
work poorly in LibreOffice Calc than in several other spreadsheet
tools. I don't know if this behavior can be "corrected" or improved.

Now, if your data is not yet imported into Calc, you can change the
type of data from "standard" or "text" to "numbers" during the
'import' procedure. This task is simple enough if the numbers are
already located under the same "column" in the csv / text file that
you use as source to import the data into Calc.

All the above comments are relevant only if you don't need to do the
conversion in a repeatedly, scripted / batch / automatic way.

Regards,
Ady.

#4

Hi,

If you are importing a txt or csv file into Calc you can use INSERT >>
SHEET FROM FILE. The first part of the wizard will you can click OK. The
second dialog has a section "Other Options". In this section check
"Detect Special Numbers". There is a preview screen which shows what the
data will look like. If the column has the same data type (numbers,
text, datetime) Calc will convert the raw text into a more appropriate
data type. Also, once checkbox is selected, Calc will remember the
setting for subsequent imports.

This may be easier than cut/paste special.

#5

There are various ways to do this, and you may want to experiment in order to discover what suits your work flow.

The VALUE() function? Yes, you can use that:
o In a spare column, row, or range (as appropriate), or even on another sheet, enter =VALUE(Xn) - where Xn is the start of the range.
o Fill the formula down the column, along the row, or through the range. You now have a copy of your data - but as numbers, not text.
o If desired, copy the numeric values and paste them back over the originals, but using Edit | Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V) instead of ordinary Paste.
o In the Paste Special dialogue, remove the tick from "Paste all" if necessary and ensure that Numbers and Formats are ticked but Formulas is not ticked.
o You can now delete the temporary values or delete their rows or columns if you wish.

No, changing the format of the cells will not help: a cell's format affects the way its value is displayed and the way inserted values are handled, but not the actual contents of the cell.

Another easy way is via a comma-separated-value file. Save the sheet with your rogue text values using File | Save As... and selecting "Text CSV (.csv)" for "Save as type:". Now open the resulting (temporary, scratch) CSV file in LibreOffice and you will have numeric values that you can copy where you need them. Yes - as has already been suggested - you could alternatively import the CSV file as an additional sheet in your existing spreadsheet document.

I trust this helps.

Brian Barker

#6

As far as I understand it, the "hidden" initial quotation mark is what
marks the contents of the cell as text. This is the same in MS Excel,
IIRC. So basically, this isn't a bug, but intended behaviour, to give
you a way to specify that a number should be interpreted as text and
not as a number.

For example, if you enter "0283", the leading zero will always be
stripped, because it is interpreted as a number and the leading zero is
superfluous, but if you enter "'0283", then this means you have entered
a text string and the leading zero is kept. The format of the cell
doesn't change this behavior, it only changes the *display* of the
contents, not the interpretation of the contents. At least, as I
understand it.

Paul

#7

If you are importing a txt or csv file into Calc you can use INSERT >>
SHEET FROM FILE. The first part of the wizard will you can click OK. The
second dialog has a section "Other Options". In this section check
"Detect Special Numbers". There is a preview screen which shows what the
data will look like. If the column has the same data type (numbers,
text, datetime) Calc will convert the raw text into a more appropriate
data type. Also, once checkbox is selected, Calc will remember the
setting for subsequent imports.

This may be easier than cut/paste special.
--

Yes, as I said, if there is still a need to 'import', that's simple
enough. But if the data is already in a spreadsheet, there is a
well-known "paste special" simple procedure. It works in other (more
than one) spreadsheet tools and usually is fast enough. The more
important matter is that LO Calc is making this simple conversion
more complicated than necessary.

I wonder if this should be reported as a bug / enhancement, so the
behavior is changed / improved. Any comment from Calc developers
would be really appreciated.

Regards,
Ady.

#8

If the numbers (formatted as text) are already saved in your spreadsheet, I would normally suggest a simple procedure involving "paste special" and multiply (by "1"). This has worked for me in several other spreadsheet tools. But Calc will (currently?) fail, because Calc adds a single quotation mark at the beginning of the cell. So what seems to be just "1" (without the double quotation marks), in Calc actually is "'1" (without the double quotation marks, but including the single initial single quotation mark).

No, the reason this fails is because you are trying to multiply text by a number. If this worked, the original problem would not exist: the user would be able to involve his text values in mathematical formulae directly. And it's wrong to think of that leading single quotation mark as actually being in the cell. Preceding a numeric value by a quotation mark is how you indicate in typing that you want the value to remain as text and that you do not want the value interpreted as a number. It's also the way that values shown in the Input Line that might appear to be numbers are indicated actually to be text. But there is no quote in the cell.

You could select the relevant cells, change their format and then 'find and replace' on that same selection. But, since this is a special (hidden) character, I'm not sure how to make it happen ('find and replace' might not find the specific character).

Indeed: it won't find it because it's not there!

As a simple user, I see this "hidden" addition of the initial single quotation mark as a _BUG_, and as one of those basic "features" that work poorly in LibreOffice Calc than in several other spreadsheet tools. I don't know if this behavior can be "corrected" or improved.

Aaargh! The quotation mark is not added to the cell, but is used to indicate that you have preserved the value as text, not had it interpreted as a number. That's most definitely a feature, not a bug! There are genuine uses for this: if you live in Newark, New Jersey, USA, your ZIP code may be the five-character text string 07102. But the postal system will not like your misrepresenting this as 7102 - the number seven thousand, one hundred and two.

Brian Barker

#9

> As a simple user, I see this "hidden" addition of the initial single
> quotation mark as a _BUG_, and as one of those basic "features" that
> work poorly in LibreOffice Calc than in several other spreadsheet
> tools. I don't know if this behavior can be "corrected" or improved.

As far as I understand it, the "hidden" initial quotation mark is what
marks the contents of the cell as text. This is the same in MS Excel,
IIRC. So basically, this isn't a bug, but intended behaviour, to give
you a way to specify that a number should be interpreted as text and
not as a number.

For example, if you enter "0283", the leading zero will always be
stripped, because it is interpreted as a number and the leading zero is
superfluous, but if you enter "'0283", then this means you have entered
a text string and the leading zero is kept. The format of the cell
doesn't change this behavior, it only changes the *display* of the
contents, not the interpretation of the contents. At least, as I
understand it.

Paul

Well, yes, but no :). In Excel and other spreadsheet tools, you
could:

1_ In an auxiliary non-formatted cell, insert the number 1.
2_ Copy that auxiliary cell.
3_ Select the cells with numbers that are currently formatted as
'text' that you want to convert.
4_ Paste special (all), multiply.
5_ Delete the auxiliary cell.

If the desired format is not just a 'general' number, then you can
format the auxiliary cell before copying it.

This simple procedure cannot be used in Calc, just because this
"hidden" single quotation mark. This procedure is successful in other
spreadsheet tools that use the single quotation mark too. So, why not
in Calc?

BTW, the quotation mark is not necessary for all cells containing
numbers formatted as text, but that's off-topic here. My point is
still relevant: the aforementioned procedure should be plausible in
Calc, just as it is in other spreadsheet tools.

There are workarounds, like using the 'VALUE' function in an
auxiliary column and then copy+paste back; yet I still wonder about
this hidden (annoying) single quotation mark.

Regards,
Ady.

#10

US zip codes (postal codes) may have a leading zero.

Why not set a cell to the formula "=right(len(CELL)-1) which returns the
all the characters except for the first.

#11

That's =RIGHT(Xn;LEN(Xn)-1) - but that won't strip the single quote mark because it's *not there*! The simpler formula =RIGHT(Xn;LEN(Xn)) would work, but that's just a complicated way of saying =VALUE(Xn).

Brian Barker

#12

Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui

All the best,
Joel

#13

Why not set a cell to the formula "=right(len(CELL)-1) which returns the
all the characters except for the first.
--

No, that won't work. You probably meant:
=RIGHT(A1,LEN(A1)-1)

where "A1" is the 'text' cell. But that one will fail too, because
the initial single quotation mark is not really part of the content
of the cell. (Note: it might work in some particular situation, but
it is not a generic method.)

The 'VALUE' method is a valid workaround, but it might be a "heavy"
method, depending on the amount of data. I would prefer the "'paste
special'+multiply by 1" method to work.

Regards,
Ady.

#14

Oh, that's easy:
1. List subscribers will have read earlier suggestions and are intelligent enough to ignore later ones if they prefer.
2. Different methods are appropriate in different circumstances - and subscribers other than the original questioner may find them helpful to their different needs.
3. It's important that questionable statements made on a public list read by many (and indeed archived) are challenged.

(Anyone is welcome to trash this message if desired!)

Brian Barker

#15

:smiley: Fair enough. I was actually just curious if there is a benefit to the formulas over the text to number as I always use it but would change if there was a benefit to some other method :smiley:

Thanks for explaining!

All the best,
Joel

#16

Why is everyone straying away from the fact that there is a simple
extension developed by Cor (one of our brilliant devs) which
accomplishes all of this? Just curious if there's a benefit to doing
these formula techniques instead of just pushing a button on a nice gui

I could ask you the same type of question regarding "paste special +
multiply by 1" method.

A user might not know of the extension, or about any extension for
that matter. A user might not use extensions, ever. Why would anyone
need to search, install and use an extension, when other spreadsheet
tools can apply a well-known "multiply by 1" method?

In other words, if very simple spreadsheet tools (not just Excel) can
use a very simple and well-known method, perhaps instead of thinking
of how to incorporate the CT2N extension into the core in Calc, the
same time could be invested to make Calc work "as expected" with
"paste special"?

To be clear, I'm not complaining, just posting my thoughts.

Regards,
Ady.

#17

Thoughts are always appreciated in our community :smiley:

Best,
Joel

#18

This will do quite easily:

Call the menu Data / Numbers as text (freely translated from my FR
install) then just click OK in the window that opens without changing
anything. This should do the trick.

Another way I often use when the import is made on a regular basis, is
to create what I call an "exploitation sheet".

You create a new spreadsheet and insert data there from the spreadsheet
you got from the import. The formulas will then look like
='file:///home/jf/Documents/Imports/MyImport.ods'#$'Sheet1'.A2
Then copy down and right and you're set.

What is interesting is that you may add some data handling when linking
the cells: you may have
=CNUM('file:///home/jf/Documents/Imports/MyImport.ods'#$'Sheet1'.A2)
which would automagically convert the text data into numerical data
without having to bother each and every time a new import comes.
You may also ignore any superfluous column, and so on.

For the ease of use, make sure to have both spreadsheets in the same
directory.

Note that:
-- this works only if the imported data is consistent (same columns at
the same place) and the data file must be uniformly named.
-- the imported data must be in a real file on disk.

HTH,

#19

ooops! forgot to specify the first step:

1. select the column to set as numbers first.

2. do that:

#20

In English, that's Data | Text to Columns... .

Brian Barker