Issue 23348 - when changing cell type from text to number ' is inserted in front of numbers
Summary: when changing cell type from text to number ' is inserted in front of numbers
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: All All
: P4 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2003-12-09 15:45 UTC by richlv
Modified: 2013-09-12 23:07 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

Testcase where user will be confused after inserting a line (8.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-08-20 17:38 UTC, emersonprado
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description richlv 2003-12-09 15:45:13 UTC
sometimes (usually in excel prepared documents) there are cells that contain 
numbers and are formatted as text. in calc these cells are not considered as 
numbers when performing calculations. now, when the type is changed to number 
calc inserts ' (apostrophe) in front of number, thus it is still stored as text 
and not used in calculations. sometimes there are a lot of cells that have wrong 
formatting and fixing this is extremly hard.

possible solutions (that could be combined) :
1. don't insert ' when changing type;
2. when opening .xls document that contains numbers formatted as text (and maybe 
included in calculations) ask if they should be converted tu numbers (could be 
very cpy consuming to analyze document and probably unnecessary);
3. add some option to choose between inserting ' and not inserting it.

probably 3rd possibility would be the best - for example, in cell properties 
when changing cell type to number having an option to convert numbers to 
"number" would be enough
Comment 1 frank 2003-12-10 08:55:27 UTC
Hi Bettina,

1 4 u.



To convert such numbers from text to number, use Search&Replace and '.*' as
search string and '&' as replace string (both without quotes). Make sure you
have tagged the Regular Expression checkbox at the Dialog.

Comment 2 ace_dent 2008-05-16 02:55:39 UTC Issue Tracker - Feedback Request.

The Issue you raised has the status 'New' pending further action, but has not
been updated within the last 4 years. Please consider re-testing with one of the
latest versions of OOo, as the problem(s) may have already been addressed.
Either use the recent stable version:
or consider trying the new OOo 3 BETA (still in testing):
Please report back the outcome so this Issue may be Closed or Progressed as
necessary - otherwise it may be Resolved as Invalid in the future. You may also
wish to search for (and note) any duplicates of this Issue that may have
advanced further by checking the Issue Tracker:
Many thanks,
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~
Comment 3 hwdooo 2009-05-12 20:47:00 UTC
Issue still exists in OOo 3.0.1 and 3.1
Does it make sense to insert an apostroph if
- the cell content is a number
- the user explicitly selects the format change from text to number?

If I change the formatting from text to number, I would expect a number not a text
Comment 4 bettina.haberer 2010-05-21 15:14:06 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements". 
Comment 5 emersonprado 2010-08-20 17:38:09 UTC
Created attachment 71224 [details]
Testcase where user will be confused after inserting a line
Comment 6 emersonprado 2010-08-20 17:53:19 UTC
Though it makes sense for the strong typing point of view, it is confusing for
the user.
I have a recurring case where I insert a line into a spreadsheet and a cell
which I use as number becomes text. Then, if I type a number there, and then
find out it's not a number, I'll change its format. But then the number is
rendered useless by the apostrophe.
Plus: since the apostrophe isn't shown, I won't tell what's wrong until I click
on the cell. Even more: searching apostrophes doesn't work as an end user
expects (one should know about the '.*' and '&' thing, and this is another
issue). That is, a regular user would get lost (as I got).

Pls see my last attachment. Cell B2 is number and cell B1 is text. If you insert
a line over line 2, cell B2 will become text. It's reasonable, since Calc has to
copy format from either the upper or the lower line, but the user doesn't have a
clue yet. So, when the user tries to use cell B2 as a number, the battle begins.
If the user inserts multiple lines, it gets worse - which is my case.

So, IMHO, Calc should either accept a number in a number-formatted cell as a
number or tell the user it'll do otherwise and offer an easy way out. In the
current way, it only makes sense to geeks :) . Pls help normal users.

Best regards
Comment 7 White Phoenix 2012-07-04 22:03:52 UTC
This problem still exists in AOO 3.4. A prompt asking if the apostrophe should be stripped from the column would be fine. And/or an easier way to remove the apostrophe (in a selected area) than using the search and replace using the regular expression would be very much appreciated. As someone mentioned, that method is fine for geeks, but not regular people.

This would probably solve issues 29891, 60110, 68652, 65510, 69067, 23348, 117470, and any others that are similar as well.
Comment 8 White Phoenix 2013-09-12 23:07:47 UTC
There is an extension that strips the apostrophes from the selected area or optionally the whole sheet from text fields (at least I do not think it will work in number fields) called Text to Number and Date. It works well although in 4.0 it no longer appears on the tool bar. If this feature were built-in to AOO that would be nice.

Although, logically, if you are convert text to numbers then you should *get* numbers not numbers marked as text. Otherwise, it makes no sense to bother changing the field.