I copy data consisting of two columns in a text editor and paste it into calc. The two columns are separated by spaces and the first column is text and the second is numeric. Like this:
When I perform the paste into Calc, it pops up the Text Import dialog. The dialog allows me to select a column and specify a data type.
I select the first column and specify a type of Text.
I perform the import and everything goes fine.
When the I examine the imported data, the first column cells are all "number" and not text as I had specified.
Since I had specified that the first column is text in the import dialog, I would expect that all the cells in the first column would have a text type.
Operating System: Windows (other)
Version: 184.108.40.206 release
Thanks for writing your report. However...
(In reply to comment #0)
> When the I examine the imported data, the first column cells are all
> "number" and not text as I had specified.
> Since I had specified that the first column is text in the import dialog, I
> would expect that all the cells in the first column would have a text type.
The formatting is Number/Default.
It has been like that for ages: at least in LibreOffice 3.3.0 it already is like that. So I guess it's simply intended behaviour. Because also when you just type 'apple' in an empty default cell, it is text, but the formatting still is Number/Default.
Thanks for the quick response, but your answer does not make sense to me. Yes, you are correct, the formatting on the pasted data is Number/Default - if that was simply the default behavior I would have no problem with that.
However, when I did the paste, Calc popped up a panel that specifically asked me what type the columns were and I specified that the column was text. I did not ask for this panel - Calc chose to pop it up when I did the paste.
So, if Calc is going to pop up a panel that asks me what type the column is, and then chooses to ignore my input, that looks, smells, and tastes like a bug.
Either Calc should not have the option to specify the column format on the import panel, or it should not ignore it. Either way, something needs to change.
(In reply to comment #1)
> Hi Marcel,
> Thanks for writing your report. However...
> (In reply to comment #0)
> > When the I examine the imported data, the first column cells are all
> > "number" and not text as I had specified.
> > Since I had specified that the first column is text in the import dialog, I
> > would expect that all the cells in the first column would have a text type.
> The formatting is Number/Default.
> It has been like that for ages: at least in LibreOffice 3.3.0 it already is
> like that. So I guess it's simply intended behaviour. Because also when you
> just type 'apple' in an empty default cell, it is text, but the formatting
> still is Number/Default.
> Best regards,
- pls respond in the Web-interface. to prevent unnecessary quoting -
Maybe there is some confusion on 'Number/Default' which is the 'setting' that allows to insert numbers, dates, text etc. and that it then is interpreted.
If you really insist on changing something here, then pls go to some die hard spreadsheet experts and discuss it with them ;)
I am not convinced that we are talking about the same thing. So let me as you this:
1) Copy the following lines and paste them into a text editor. Then copy the same lines from the text editor. (If you copy/paste directly from a web page it will behave differently, but that is a different issue).
2) Paste into a new worksheet in Calc.
Calc immediately pops up a modal panel labeled Text Import.
3) On that panel select "Space" as a separator. Now in the field display you will see two columns. The first containing text strings and the second containing numbers.
4) Click on the header for the first column. The column type pulldown will say "Standard"
5) Select "Text" from the pulldown.
6) Hit OK to import the data into the spreadsheet.
So, my question is this: What is the expected behavior of my having selected "Text" from the pulldown? I have been laboring under the assumption that making that choice tells calc that those are "Text" cells. If that is not the case, then I do not understand what the purpose of that "column type" pulldown is.
(In reply to comment #4)
> I am not convinced that we are talking about the same thing. So let me as
> you this:
You initial explanation was very clear. I did exactly that :)
(In reply to comment #5)
> (In reply to comment #4)
> > I am not convinced that we are talking about the same thing. So let me as
> > you this:
> You initial explanation was very clear. I did exactly that :)
So, could you please do me a favor and reply to the question at the end of my last comment: What is the expected behavior of changing the "column type" on the panel that pops up? What does that option do?
(In reply to comment #6)
> So, could you please do me a favor and reply to the question at the end of
> my last comment: What is the expected behavior of changing the "column type"
> on the panel that pops up? What does that option do?
Mark the second column as text, and see what it does...
> Mark the second column as text, and see what it does...
It does exactly what it should do. It changes that column to text values and if, after the import, I right click on a cell in the second column and say Properties, it tells me its a text cell.
However, if I do the same thing on the 1st column, it does not change the cells to text cells, it keeps them as number cells.
Try it. Mark both columns as text and after the import check the cell properties. If have tried it numerous times and it does not work for that first column.
Because that is needed to make text from a number.
Pls see my comment #3.
(In reply to comment #9)
> Because that is needed to make text from a number.
> Pls see my comment #3.
However, in one case it is setting the format of the cell and in the other it is not. Why is Calc setting the format of the cell for column 2 and not for column 1?
That is inconsistent. For one column Calc does what the user has asked it to do, but for another Calc silently ignores the users input?
If selecting "Text" for column sets the format for the cells in column2 to text, then it should do the same for column 1.
If selecting Text is going to continue being ignored on Column 1, then it should be removed from the pulldown for that column.
I have experimented a bit more with mixed data in the columns and now I understand what is happening:
When you select a column and mark it as "Text", Calc goes through and identifies all the cells that have numberic data in them and changes the format to "Text", but it leaves all the cells that have non-numeric data alone and leaves them as "Number".
The result is that the spreadsheet now has a column with mixed data in it.
The reason that this behavior is annoying and why I consider it a bug is this:
When I imported the text column, in the import column I marked it as text and thought I was done. It was only later than I realized that sorts on that column were not sorting that column properly. They were in a bizzare order and I started digging into the problem.
The way the current functionality is set up means that the "column type" setting in the import dialog is essentially useless because you are not guaranteed that it will actually set the column type. After the data has been imported, you have to go an set the format of the cells again.
If you mistakenly believe that the column type setting in the import panel actually sets the column type, then none of your data sorting will work properly for text data.
I can understand the feeling because of this behaviour.
There are more topics with the default cell and how it works. If you are interested: take a look at the discussion round automatic date/number recognition and the work that our hero Eike did recently, to make that less intrusive and more flexible at the same time.
For the rest: see my comment 3 ;)
Needless to say, Excel does it right.
Maybe one of the commenters here could be so kind to test with the new possibilities in Tools > Options > Calc > Formula .. Detailed calculation settings.. Custom Convert Text to Number?
(In any case in 4.3 and 4.4)
(I think it does not change the behaviour, but one never knows..)
Dear Bug Submitter,
This bug has been in NEEDINFO status with no change for at least
6 months. Please provide the requested information as soon as
possible and mark the bug as UNCONFIRMED. Due to regular bug
tracker maintenance, if the bug is still in NEEDINFO status with
no change in 30 days the QA team will close the bug as INVALID
due to lack of needed information.
For more information about our NEEDINFO policy please read the
wiki located here:
If you have already provided the requested information, please
mark the bug as UNCONFIRMED so that the QA team knows that the
bug is ready to be confirmed.
Thank you for helping us make LibreOffice even better for everyone!
Okay, I'm at a bit of a loss as to why I've been asked for comments. I use Linux, not Windows and I'm not using the same version Office but I can say in ver 220.127.116.11, the behaviour is the same at reported by the submitter. As far as I am concerned, the bug is confirmed and it make no sense to me, to offer the option to to set a column formatting and then ignore the user's request simply because that column doesn't contain non-text data. If the user specifies a specific format, Calc should not examine the data but simply comply.
status NEW because of confirmation in previous comment.
reverted version to earliest where the bug was reported (4.1.0)
please tell if problem persists in latest 4.3.7 and 4.4.3 releases
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present on a currently supported version of LibreOffice
(5.1.5 or 5.2.1 https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the version of LibreOffice and
your operating system, and any changes you see in the bug behavior
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave
a short comment that includes your version of LibreOffice and Operating System
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword
Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
This is not a regression issue. Behaviour is the same in ver 3.3.0, never-the-less, I might be expecting something different from TEXT than from STANDARD. Maybe it doesn't do anything because the first column is already TEXT.