Issue 14761 - fail to format cells on columns with intermixed datatypes.
Summary: fail to format cells on columns with intermixed datatypes.
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.2
Hardware: All All
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2003-05-22 21:10 UTC by Unknown
Modified: 2013-02-07 22:32 UTC (History)
1 user (show)

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


Note You need to log in before you can comment on or make changes to this issue.
Description Unknown 2003-05-22 21:10:43 UTC
This is a design flaw in setting the cell datatype on columns of intermixed 
datatypes.  Example: A column name 'product code' containing entries of 
numeric and alphanumeric data.  When using the format cell option to 
format "numeric" data to "text" format these changes do not actually hold.  
This causes problems when attempting to import the spreadsheet data into a 
relational database table, where fields/columns can not contain intermixed 
datatypes.  This is a design flaw which is present in MS Excel applications as 
well, however MS Excel does let you set the datatypes on the individual cells. 
The Open Office Spreadsheet application does not.  I have published an article 
which covers the problem with intermixed data at
To correct this problem, a user should be able to select a column (parent), 
and select a datatype apply to 'all' the cells (children) of the column.  From 
experience in dealing with intermixed data, some types of information such 
as 'product codes' contain intermixed datatypes.  A value of "13004" in a cell 
can indeed be valid "text" data.  There are a great number of people which are 
frustrated by how difficult it is to import Spreadsheet into Relational 
Comment 1 frank 2003-05-23 12:24:22 UTC

this is not a bug but an Request for enhancement, so I set the flags.

As a workaround (only possible for versions greater 1.1 Beta):

Select the cells you want as text, choose format cells and the format
option text.
Open the Search & Replace dialog and put a dot '.' into the search
field, a '&' at the replace field and tag the checkboxes for regular
expressions and in selection only. 
Press Enter and all values are converted to text now.

As the regular expressions are implemented from OOo1.1Beta2 this will
only work with versions greater OOo1.1 Beta.

Best regards

Comment 2 frank 2003-05-23 12:24:48 UTC
Hi Bettina,

1 4 u

Comment 3 ace_dent 2008-05-16 02:39:11 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 4 bettina.haberer 2010-05-21 14:41:03 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements".