CALC convert text to numbers

users
#41

Try it, or I can send you a spreadsheet that fails to change the
fonts and you can sew what I mean.

I just tried it on a simple spreadsheet. Running LO 4.1.1.2 on Windows
7. I put some random text and numbers into a new spreadsheet, and
changed some of the fonts and sizes, then clicked in the top left
corner of the sheet to select all cells, changed font and size, and all
cells changed to the new font and size. Then I changed font and size
again, and again all cells changed to the new font and size. Seems to
work fine for me.

Perhaps put your example somewhere I can grab it, and I can test it if
you want.

#42

Paul,

Make this test.

1. Open a new worksheet. Format some cells, say from A5:A10 as text.
Write some numbers on those cells.

Ok

2. Copy those cells to other column, say to C5:C10 and format as
number (format @).

The '@' format is text, so I'm not sure what you want me to do, format
as text, or format as number?

You will see that the cells now shows an ' before the numbers.

Yes, if I format as number, this is correct.

3. Write a formula in other cell, multiplying with 1, for instance,
E5=C5*1

Ok

Now check the cell and you will discover that you have a number.

True enough, when I copy these cells, and paste special, pasting only
text and numbers, not formulas or all, then I get numbers in the cells.

Therefore, those numbers with ', that in reallity are text, can be
multiplied by 1, to transform it to a number.

As Brain explained to me, an implicit VALUE() must be done on the text
when multiplying by 1.

As a side note, I was not able to use the method of copy -- paste
multiplying by one.

How do you mean? When is the multiply done? If I copy the numbers from
the cells with the formulas it works fine.

So now I'm confused, if this *does* work, why was Ady complaining
about it not working?

#43

Well, the procedure I mentioned involves copying one cell only, which
means it is a faster method, as oppose to having to copy perhaps a
lot of cells (e.g. a "whole" column; or having to add many new
"VALUE" formulas; or having to add a new column and multiply by 1
each cell and then paste special...).

I repeat the procedure that I posted before (which works correctly in
other spreadsheet tools):

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, you could
optionally format the auxiliary cell before copying it.

Although the "Tools -> Text to Columns..." method in Calc is nice,
the procedure I am describing is more flexible. Unfortunately, it
currently doesn't work in LibreOffice Calc 4.1.3.2.

Regards,
Ady.

#44

Well, the procedure I mentioned involves copying one cell only, which
means it is a faster method, as oppose to having to copy perhaps a
lot of cells (e.g. a "whole" column; or having to add many new
"VALUE" formulas; or having to add a new column and multiply by 1
each cell and then paste special...).

I repeat the procedure that I posted before (which works correctly in
other spreadsheet tools):

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.

Hunh! Well, you live and learn. I didn't know you could do this.

I see that this doesn't work, although using a formula of cell*1 does
work. In other words, when using a formula, if a cell is given as
input, and is text where a number is expected, an attempt is made to
use VALUE on the cell. If copying and pasting, the attempt is not made.

I'm not sure it is correct to ever do an implicit VALUE, but there is a
good argument for making this copy paste method consistant with the
behaviour of the function method.

Although the "Tools -> Text to Columns..." method in Calc is nice,
the procedure I am describing is more flexible.

I'm not sure why you say it is more flexible. Text to columns would be
the "correct" way of doing this, and I can't right now think of a
case where you would need your way. Can you give an example?

#45

> Although the "Tools -> Text to Columns..." method in Calc is nice,
> the procedure I am describing is more flexible.
I'm not sure why you say it is more flexible. Text to columns would be
the "correct" way of doing this, and I can't right now think of a
case where you would need your way. Can you give an example?

--

First, evidently I meant to "Data -> Text to Columns..." (not
"Tools"...). My apologies.

Currently in LO Calc 4.1.3.2, the "Text to Column" method will let
you convert the text into one of a few specific number formats. If
you want to convert to other formats (e.g. percentage, scientific, or
some accounting type, or...), then "paste special, multiply by 1"
*should* let you do it.

Also, if the 'text' cells contain additional characters such as
thousand delimiters and the like, it might be possible to reduce the
necessary steps to obtain the desired format conversion. But since
this well-known method is currently unavailable in LO Calc, all this
is "wishful thinking" only.

I could give more examples, and of course that you could get the same
result by using several steps.

On one hand, this method is effective, efficient and well-known in
several other spreadsheet programs, and for users that already know
it, it seems at least strange that LO Calc doesn't support it. (BTW,
I still think something "fishy" is happening with the single
quotation mark in LO Calc.)

On the other hand, there are (less efficient) alternatives to
eventually get to the same final result in LO Calc. Seeing the
current ratio of bug reports vs. bugs resolved, where in many of them
there is no alternative available so to get the desired result using
LO Calc, I am slightly reticent to actually report this as a bug (or
as a potential enhancement, whichever the adequate term would be for
this case). When other issues regarding paste (and/or paste special)
get to be resolved, perhaps then this issue will get improved too.

Regards,
Ady.

#46

These solutions are far too complex especially if you have very large sheets.
Why not use the extension CT2N?
It is simple, very straightforward and you can decide ,whole sheet, or just
parts.
The ' is just removed.
I understand it is to be included as standard, in which case there will be
no need to install the extension.
You can find it here:
http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

Tink.

#47

Hi Ady,

On one hand, this method is effective, efficient and well-known in
several other spreadsheet programs, and for users that already know
it, it seems at least strange that LO Calc doesn't support it.

I'm still not sure it is "correct" behaviour, but you do have a strong
argument.

(BTW, I still think something "fishy" is happening with the single
quotation mark in LO Calc.)

The single quote is a red herring. It is correct behaviour, and the
same as other spreadsheet software. It is the implicit conversion
of the text to a value that differs.

On the other hand, there are (less efficient) alternatives to
eventually get to the same final result in LO Calc.

And perhaps more "correct" ones. Correct in the sense that I'm not sure
implicit conversions should be done on values. But it is a convenient
shortcut...

Seeing the current ratio of bug reports vs. bugs resolved, where in
many of them there is no alternative available so to get the desired
result using LO Calc, I am slightly reticent to actually report this
as a bug (or as a potential enhancement, whichever the adequate term
would be for this case). When other issues regarding paste (and/or
paste special) get to be resolved, perhaps then this issue will get
improved too.

Yeah, there is something funny up with cut and paste, I'll post in a
separate email, but I'm guessing the system needs an overhaul, which
might mean this is looked at at the same time.

Paul

#48

It's worth mentioning that this is a misunderstanding of what is happening. There is no apostrophe in the cell to remove. The apostrophe indicates (in the Input Line) that what may appear to be a number is actually text. After conversion it is no longer appropriate, so it doesn't appear.

If you have "1234" as text, you have a four-byte character string. If you convert that (or the extension does) to a number, you get a (very probably) eight-byte floating point number, consisting of a mantissa and an exponent, each with its sign somehow encoded. There won't be a 1, a 2, a 3, or a 4 anywhere to be seen: only the composite number. That's very different, even though the formatted appearance in a spreadsheet cell could be identical. It's because these cell contents are very different that the apostrophe is necessary as a warning.

Brian Barker

#49

I have forgot in my previous post, another option:

First copying the text to convert [Ctrl+c].

From the right-arrow in the icon. selecting unformatted text, we get the
same option as importing csv files.

<http://nabble.documentfoundation.org/file/n4083240/Captura.png>

Miguel Ángel.

#50

These solutions are far too complex especially if you have very large sheets.
Why not use the extension CT2N?
It is simple, very straightforward and you can decide ,whole sheet, or just
parts.
The ' is just removed.
I understand it is to be included as standard, in which case there will be
no need to install the extension.
You can find it here:
http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

We are going in circles. The extension not always works (see bug
reports), and the supposedly "too complex" solutions you are
referring to take a few seconds and _less_ than 10 clicks.

Regarding including the extension as standard, I hope the devs are
using their valuable time to take care of features that _really_ have
_no alternative_ in Calc.

Regards,
Ady.

#51

Hi Ady,

> On one hand, this method is effective, efficient and well-known in
> several other spreadsheet programs, and for users that already know
> it, it seems at least strange that LO Calc doesn't support it.
I'm still not sure it is "correct" behaviour, but you do have a strong
argument.

There is no "correct" behavior for this. The possibility to interpret
/ parse the content of a cell as a number (e.g. for usage in other
cells) even when the original cell is formatted and displayed as
'text' is a feature, which is already present in other spreadsheet
programs. A theoretical requirement to have to *always* use "VALUE"
for any and all usage of those 'text' cells would trigger a natural
enhancement request to make it easier. You can use "VALUE" if you
think it is needed for some situation, but experience says that there
are many simple cases where imposing its usage would negate common
sense. There is a reason why such common feature has been present in
spreadsheet programs for decades now.

Perhaps thinking about it this way might help:

1_ In an auxiliary 'number' cell, insert the number 1; [ENTER].
2_ Select that auxiliary cell.
3_ Copy that auxiliary cell.
4_ Select the cells with numbers that are currently formatted as
'text' that you want to convert.
5_ Paste special (all), multiply; OK.
This "paste special" step is performing the following actions:
5.1_ It pastes first the "cell format" from the auxiliary cell,
converting the selected cells from 'text' format to a new 'number'
format; and,
5.2_ It multiplies the content of the selected cells by the content
of the auxiliary cell (by "1" in this case); and,
5.3_ It adds all other characteristics of the auxiliary cell to the
selected cells (e.g. comments).
6_ Delete the auxiliary cell.

To be clear, the 'text' "format" of the cell shouldn't block the
multiplication (in the above procedure or when using its content in
other cells), since the *content* of the cell should be independent
of the way it is being displayed in the cell. You should be able to
use that same content in whichever way you want, whether you display
the cell with leading zeroes, decimal places, as text, or in yellow.

I don't know if LO Calc actually performs these actions in this way
and order. I am just trying to explain why this feature makes sense
(as it does in other spreadsheet tools).

> (BTW, I still think something "fishy" is happening with the single
> quotation mark in LO Calc.)
The single quote is a red herring. It is correct behaviour, and the
same as other spreadsheet software. It is the implicit conversion
of the text to a value that differs.

We are going in circles. The example that "Denis Navas Vega" already
gave (with steps that you already followed) shows that the initial
quotation mark is not necessary when you format the cell as 'text'
before inserting its content. We all agree that the initial single
quotation mark should not be part of the cell content; it's just an
optional "formatting aid" to be used when fits the need.

Yet, when in LO Calc you manually convert a cell (that you first
formatted as 'text' and then inserted a pure number, without "'")
from 'text' to 'number' (with ctrl+1, number, general), the
*previously nonexistent* initial quotation mark is *kept* (or rather
*added*); it shouldn't!!! (a.k.a. BUG)

That's the difference between LO Calc and other spreadsheet tools
where the "multiply by 1" conversion works as expected.

> On the other hand, there are (less efficient) alternatives to
> eventually get to the same final result in LO Calc.
And perhaps more "correct" ones. Correct in the sense that I'm not sure
implicit conversions should be done on values. But it is a convenient
shortcut...

See my prior rant about non-existent "correct" behavior vs common
sense feature for this case.

Regards,
Ady.

#52

Hi,

It's worth mentioning that this is a misunderstanding of what is
happening. There is no apostrophe in the cell to remove.

Yes there is!

It's because these cell contents are very different that the
apostrophe is necessary as a warning.

It´s not a warning, it´s an operator.

It's somewhat like the = at the beginning of a formula.

Cheers,
Stefan

#53

To be clear, the 'text' "format" of the cell shouldn't block the multiplication ...

"Text" isn't (just) a format: it's a separate data type. You can format cells as number or text, but that doesn't change what's stored in them (except that it governs how input typing is interpreted). Your desire that text *data* should be a permissible argument in mathematical formulae is a preference, of course: others may prefer that it wasn't.

... since the *content* of the cell should be independent of the way it is being displayed in the cell.

That's true, but you miss (or avoid) the point that in spreadsheets generally numbers and text are different data types of cell content. How you display the text string 1234 or how you display the number 1234 should indeed not affect the content of either cell, but it remains that the two data items are quite different. This is different from date, time, percent, currency, and so on, where what is stored is indeed just a number and the date-ness, time-ness, etc. exist only as formatting.

You should be able to use that same content in whichever way you want, whether you display the cell with leading zeroes, decimal places, as text, or in yellow.

In the situation we are discussing, it's simply not the "same content": spreadsheets allow storage of text and numbers as separate concepts. In asking for text that looks like a number and the equivalent number itself to be treated interchangeably, you are arguing for "weak typing". There are arguments too for strong typing. But surely in most information contexts, text and numbers at least are handled and typed differently? See http://en.wikipedia.org/wiki/Strong_and_weak_typing .

All this is not to say, of course, that you cannot allow implicit conversions: it's OK to want a spreadsheet to interpret some text as the equivalent number when the value is invoked in a mathematical context, but it is important to understand that such conversion is taking place.

It's perhaps worth rehearsing here the real fundamental point (which may take some thinking about): that you can never enter numbers into a computer via a keyboard, since a keyboard handles only characters. If you type the three characters "1.2" into a cell, a (complicated) conversion from the character string to the single number they represent is performed for you - unless you choose to inhibit this by adding the leading apostrophe.

Brian Barker

#54

It's worth mentioning that this is a misunderstanding of what is happening. There is no apostrophe in the cell to remove.

Yes there is!

Sorry, but that's simply untrue - and you can easily show it. Type '1234 into a cell, so that you get the four-character text string 1234 in the cell (not the five-character string '1234). Now put =LEFT(Xn;1) in another cell - to extract just the first character. According to your theory, this formula should evaluate to just the apostrophe - or perhaps you think that the apostrophe would be suppressed and you would see nothing. But neither of these is that case: instead, you see the true first character, "1".

It's because these cell contents are very different that the apostrophe is necessary as a warning.

It's not a warning, it's an operator. It's somewhat like the = at the beginning of a formula.

It's an operator when you include it in typing into a cell: it ensures that what you type is stored as text and not converted to a number. But it's surely not an operator when it appears in the Input Line. If it were an operator there, what operation do you think it would perform?

Brian Barker

#55

Type
'1234 into a cell, so that you get the four-character text string
1234 in the cell (not the five-character string '1234).

I you do this, the content of the cell will be '1234 and the cell
will display the text 1234 as a result.

Now put
=LEFT(Xn;1) in another cell - to extract just the first character.
According to your theory, this formula should evaluate to just the
apostrophe

No. According to what the programme does, you will get the character
1, because it is the first character of the result in cell Xn.

But it's surely not an operator when it appears in the
Input Line.

Yes it is. Just like an = tells the programme that the following has
to be interpreted as a formula, the ' tells the programme that the
following has to be interpreted as text.

Stefan

#56

Ok, I can't seem to refute this, because it would work exactly the same
was as what I and Brian described works. The only difference would be
in the internal representation of the cell contents, and I am not about
to go diving into the code to check that out.

Either way you think of it you will still see that it functions exactly
the same, and the behaviour is correct, and consistant with other
spreadsheet programs.

Paul

#57

Hi Paul,

the behaviour is correct, and consistant with other spreadsheet
programs.

I never doubted. :slight_smile:

Stefan

#58

Sorry, no. The behavior may be correct, but it is not completely consistent with other spreadsheet programs. At least it is not completely consistent with how Excel implements this. In particular, I still am unable to simply paste special a cell containing the number 1 onto a cell (or range of cells) containing a text representation of a number and, using the multiply choice, have that cell's contents converted to a number from text. This is what is done widely among Excel users.

Granted the procedure is not intuitive, but it is widely known and used. You may argue that it is behavior that a spreadsheet should not allow and I could be persuaded to agree, however, Libreoffice's behavior here is not consistent with other spreadsheet programs in regard to this feature.

In my opinion, Libreoffice does not have to do everything the way Excel does it, but when it differs, we should be clear that it differs.

Lastly, this thread has been very valuable to me in that I now have several alternate methods of handling columns of numbers which are wrongly formatted as text. I deeply appreciate this resource.

Charles

#59

You are confusing two issues here. The first regards the apostrophe
marking cell contents as text when they appear to be numbers. The
second is the issue of allowing pasting and multiplying over cells
containing a text representation of a number.

You are correct in that the second issue is not consistent with Excel.
However, I was speaking specifically of the first issue when I said
that the behaviour is correct and consistant. In this discussion a few
people have confused the two, thinking that the apostrophe is somehow
connected to, and possibly the cause of, the second issue. I'm trying
to say that the apostrophe is a separate issue, and is *not* a bug, as
some have suggested. The apostrophe shows correct behaviour, purely in
and of itself. As a separate issue, Calc does not allow one to paste
and multiply over cells that contain numbers as text, and that,
completely aside from the issue of the apostrophe, is inconsistant with
Excel.

Just trying to clear up some of the confusion.

Paul

#60

I've been following this debate with great interest.

One big question comes to mind: Why would someone use the apostrophe construct in the first place if he intended to perform arithmetic calculations using the cell content? I understand ending up with a text string rather than a number by forgetting to use VALUE() on a substring in a formula but even that seems to fall into the category of a "cockpit error" rather than an "aircraft design flaw" as is being implied on one side of this debate.