Split to columns converts data to dates

[theYannis]

Hello
Auto-recognition of all numerical data to dates if possible is universal bug to calc.
I thought this happens only during typing though. Not the case.
I was trying to copy some coordinate data from notepad to calc to split them in columns. When I splited them, some of the data changed to dates. Converting to text is not going to help because I need to use them as numbers and do calculations with them (areas, distances, etc).
Converting to plain numbers will not work as well, because I can't make it permanent. Calc will always reset the cell to date, no matter what I do.
Single cells during typing, I can handle in general, because I start to learn when the bug will trigger and try to avoid it, or when I fail to do so, I see it immediately.

This is not efficient though when I deal with whole rows or tables, already imported that I want to split in columns. Is there any work around in that, at least for the split to columns command or in cases I copy data from somewhere, that they are "obviously" they way I need them to be.

Also, as coordinates data had only one separator in comparison to dates that have two. Still calc couldn't tell the difference.

I know this debate regarding date auto-recognition is 13 years old or so.
There had been suggestions, such as:
- increase the sensibility of date recognition (perhaps to have exact two same separators xx/xx/xx or xx.xx.xx, etc)
- limit the feature to during typing only
- make the writer on-off recognition available to calc, etc
- even someone suggested to use libre office, because they have fixed their program (is that a solution though?)

the most sufficient way to avoid turning your data tables to calendars at the moment is to use pre-2004 versions, throwing away all the work that has been done so far to improve those programs. I don't believe this is also a good thing.

What I haven't tried is to use several different versions of the calc to dodge bugs that errupt but keep using the new tools.

[Zizi64]

There are many ways to eliminate the number recognition - if you want use the typed-in string permanently:

- Performat the input cells by a Cell style adjusted to Text format (before you type the text).
- Use a ' (apostrophe) delimiter character before the string. (It will eliminate all of recognitions, and the ' character will not be appeared.)
- Use the converted numerical date values - formatted by well adjusted Cell style. (after you typed in the texts)

[Villeroy]

Format target column as text or mark the column in the preview as text.

[theYannis]

- preconvert cells to text partially works, since I can still do some calculations with them. SUM function doesn't work though, maybe some other functions don't either.
I need to set the cells to a numeric type of data and keep that setting, but I believe it is a bug of calc, that can't keep the format you give to it (even manually).

- apostrophe and other such tricks work while typing single cells, but my thing was copy-paste larger amounts of data

I appreciate the effort made by the users to help each other. I hope at some time there will be a patch or update that fixes what was originally working, years ago.

[Villeroy]

A portion of text may represent either a number or a date. Within the same locale context is impossible that any number is interpreted as date. Since you do not tell us the locale context nor the text, it is impossible to give any further advice.

[keme]

Can you supply a few sample source lines (how it looks before split)? There may be an obvious solution to the issue, which dawns upon one of us when we see the source.

[Villeroy]

- even someone suggested to use libre office, because they have fixed their program (is that a solution though?)

Of course this is a solution. LibreOffice is the successor of OpenOffice forked from the same code in 2010. As it seems by now, there is no more development on OpenOffice.
I use LibreOffice and it behaves very much the same. 2 or 3 groups of digits separated by a date separator are interpreted as a date. 2 groups of digits separated by the current locale's decimal separator are interpreted as a decimal. If you don't want that, there is always a way to supress it and get text in return. The date separator and the decimal separator are never the same within the same locale.

[theYannis]

Of course this is a solution. LibreOffice is the successor of OpenOffice forked from the same code in 2010. As it seems by now, there is no more development on OpenOffice.

thanks for the information. didn't know that

[Villeroy]

We still don't know any string you are going to split nor do we know the locale context (Greek with comma decimals?).
See Tools>Options>LanguageSettings>Languages>Locale (second option from top)

[theYannis]

My local is Greek and the decimal is ",". From my other file, due to English local (it was not an export from a program installed in my PC), I had "."
Still my argument is that I had only one separator in my string and dates have two. It shouldn't change to date.
I believe is wrong coding.
But since openoffice is not active (coding wise) anymore, I will try libre office as suggested. They might have the on/off recognition available at least.
Recognition success ratio on me is about 5%. I am too unpredictable for the coders

[Villeroy]

They might have the on/off recognition available at least.

On/off recognition is column type "Text" in the import dialog or number format "Text" in the target column.
With recognition = off you get text.

We still do not know a single snippet of text you are trying to split. I guess it is something like "abc 1.12" and you want 1.12 as a decimal number? Switch to English locale. The target cell's number format language won't do. You have to use the general locale and switch back when you got the correct numbers in the target cell.
LibreOffice has a function NUMBERVALUE("1,234.99"; "." ; ".") where the second argument is the decimal separator and the third one is the thousands separator. This function can convert text to numbers even when the locale does not match with the locale of the numeric string.

[theYannis]

the string I tried to split was something like "12.3000 56.2549 32.9900", which is coordinates.
On splitting the first piece turned to 12/30/2000 instead of 12.3000. I swap between calc and notepad 1 couple of times pasting around tables, because there is a conviniency in giving my coordinate data the form I need and it helps me make calculations in calc and switching among locals to use the coordinates in tables and some websites.

So it is important to turn date recognition to plain number recognition and not using text to do it. Both text and date cannot be used in calculations. I find ways work around it, but it is less productive. I will try libre office. If I could force the "numbervalue" to the default template it will be perfect I think.
Does libre office has the same files as openoffice?

[Villeroy]

-- Preferable Method --
Import the coordinates correctly so you get whatever you want in 3 separate columns without having to repair them.

-- First Repair Method with Split to Columns --
Split to columns
Click the top-left corner of the preview and select "English(US)" from the list box.

-- Second Repair Method with Split to Columns--
Split to columns
Click the top-left corner of the preview and select "Text" from the list box.
Replace . with , if you want decimals.

-- Third Repair Method with Split to Columns --
Switch to English locale.
Split to columns with default settings or as text if you want text
Switch back to Greek locale.

The VALUE function can also convert the split strings if you substitute the . with,
=VALUE(SUBSTITUTE(A1;".";","))
or if you run LibreOffice:
=NUMBERVALUE(A1;".")