Issue 57095

I checked with 2.0 German version WIN XP: [680m3(Build8968)] and can confirm
that problem. Same with 1.1.4 (German) WIN XP: [645m52 (Build 8824)].

This is known from issue 24374, where you will find a workaround. We shoud think
about that WONTFIX again, what imho causes a very user unfriendly behaviour of
OOo. Can anybody tell us a good reason for that funny behaviour in OOo?

Hi,

this *is* a double to Issue 24374 .

And it's not unfriendly. Think about a sheet formatted as text one of these
cells is referenced on an other sheet with an vlookup based on textdata. Now you
format the referenced cell as number and the text changes to that. As a result
the vlookup does not solve correctly and you get in trouble.

This is the fact that makes this Issue a wontfix Issue. Also the conversion of
such values to a real number is easy by using find&replace with regular expressions.

Frank 

*** This issue has been marked as a duplicate of 24374 ***

closed double and wontfix

Hi,

how does one use "find&replace with regular expressions"?
how will the common person know abt this?
if there are 2 cells A1 having '100 and A2 haing '200, and now I want to change
only A1 from text to number, how to do it? note that I dont want to change the
A2 column formatting.

dont give some technical reasons for wontfix. i didnt know that the volunteer
programmers would be having the same attitude of the pay programmers of my comapny!

~A.

To get rid of the apostrohe just use Find & Replace search for '.*' replace with
'&' and set regular expression under More in the Dialog.

Hm, of course, that example in comments from fst Tue Nov 1 00:50:21 -0800 2005 
 hits, but the question is how often that OOo behaviour will save users from
_that_ problem and how often users will run into problems because they never
heard of that "search and replace trick". Of course, there are many other
possibilities to get that "text numbers" with "'" to "real numbers" again, for
example you can cut, change cell format to "number" and paste special as
"unformatted text" again ...

Is there anyone who can contribute some document where this special behaviour
and the difference between 
 - Numbers inserted into text formatted tells, which become that ' in front of
   them if you try to change them to format "Number" and what can not be used
   for  calculations
- Numbers formatted to "Text" after they had been inserted, what can be 
  rechanged to format "number" without problem and what can be used for 
  calculations even during being in text format without problem?

Thanks

Rainer

"heard of that "search and replace trick". Of course, there are many other
possibilities to get that "text numbers" with "'" to "real numbers" again, for
example you can cut, change cell format to "number" and paste special as
"unformatted text" again..."

I downloaded OOO and was using it. This is a real world scenario, wherein I was
entering my financial details and hit this WONTFIX of ooo. 
This occured in 1st hour of my usage of OOO.

There would be many such cases of various people.
Please try to understand that these are genuine problems of real world people -
who are least interested in the documentation or the technical details.

Consider a huge data size. Your cut paste solution wont work in a real world
senario.

Also, I have seen people use excel with minimum size of 5 to 10 MB. OOO guys
cannot consider people to do a cut/paste or anyother workaround.
We need to take MS Office headone to succeed.

What is the bahaviour in latest staroffice?

This weekend I will check the working on the spreadsheet provided by kubuntu.
You can also try it and let us all know how does this particular case work.

I have heard that various comapnies have made MS Office an OS in itself and have
created a strong ecosystem around it. People are selling financial charts and
formulae, legal stuff all based on MS Office.

I hope that you understand what I am trying to explain.

"Now you format the referenced cell as number and the text changes to that. As a
result the vlookup does not solve correctly and you get in trouble."

A simple regexp will help you solve whether to keep - or to be precise -
"consider" the cell contents as text or as numbers.

Forget the "vlookup". Consider this simple case in excel (this simple case would
be applicable to OOO as well -->
In excel 2000 enter 123 in cell A1 and abc in cell A2. 
Both of them are general.
Now change the A1 to text and A2 to number and check the cell contents.
Using ' in front of numbers was an old paradigm.

Each cell's datastructure will have a member variable called format. If format =
text, using text rules, if format = number and regexpression shows that its a
number, use the cell as number - else use it as a text - or any other format
that its forced into.

Some other "workaround", a macro changing "real text numbers" to "looking as
text numbers", you fin here: <http://www.winnirohr.de/ooo/makros/calc_Txt2Num.sxc>

"Regina Herschel" told me a source where you can read about all those formatting
issues:
<http://specs.openoffice.org/calc/ease-of-use/enhance_number_recognition.sxw>

Hi,

this is a double to Issue 24734 and closely related to Issue 5658.

So I close it again as double.

Frank

*** This issue has been marked as a duplicate of 24734 ***

closed double

fst: I think you got the issue number wrong.  Issue 24734 could not be the
original issue of this one.

Yes,

mixed up the numbers. It's a double to the before mentioned Issue 27374 and I
close it again as such.

Frank

*** This issue has been marked as a duplicate of 24374 ***

closed double