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,
Cor

Hi Cor,

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.

Marcel


(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,
> Cor

Hi Marcel,

- 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 ;)
Cheers,
Cor

Hi Cor,

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).

DDDDD 3
DDDDU 4
DDDDd 13
DDDDu 4
DDDUD 27
DDDUU 10
DDDUd 36
DDDUu 16
DDDdD 26
DDDdU 36

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?

Thanks,

Marcel

(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.

Best regards,

Marcel

Hi Marcel,

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 ;)

Regards,
Cor

Needless to say, Excel does it right.
Reopening.

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..)

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 4.2.8.2, 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

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.