[Issue] No "Off" Option for Automatic Date Changes

[jrkrideau]

I have changed the format of the cell I want text in to text as advised.

Despite this, OO is insisting on auto-correcting the text I entered.

Please advise.

Did you format the cells before you entered the data? It works fine for me.

[acknak]

Changing the cell format does not change the way Writer interprets what you type. If Writer sees that you type something that looks like a date, it will convert your typing to a date regardless of what the table cell format is set to.

You have to turn number recognition off (see above) to prevent Writer from trying to interpret what you type in the table cells.

Edit: PS:

Sorry for any confusion, this thread deals with Calc, but some posts above are using Writer and the latest poster does not say which is being used. The two applications behave differently.

[JohnnyHands]

[UPDATE: There's an easier solution than I've written in this post -- using Edit -> Paste Special... (see my post two below this one for the Paste Special method, and thanks to Villeroy for pointing me in that direction) ]

After changing the cell number formatting to text in Calc, and copy-and-pasting the data, sometimes the date re-formatting is quelled and sometimes it isn't (so neither faction on this thread is crazy.) I think it has something to do with whether the text your copy-and-pasting is plain text or rich text. Plain text seems to quell the date re-formatting - which is what you want, and rich text appears not to - though I'm no authority on what is and isn't rich text, since I can't see the actual bytes being copied.

My solution was to, after changing the cell number formatting to text, first copy-and-past my data from my browser into BBEdit (on Mac OS) which takes in the data as plain text, then copy-and-paste it from BBEdit it into Calc. Voila! If you don't have BBEdit, I'm not sure which other Mac program you could use. Anyone? On Windows, anyone?

I believe this issue deserves closer scrutiny (it's a bug) as the type of text should NOT be the difference in date reformatting or not date reformatting. That's screwy!

---

Here's where to find the exact text I used, to see for yourself that the date reformatting is, indeed, not quelled (at least when copied directly from my Safari browser into Calc):

Go to d2football dot com, and in the left column, click on TOP 25 POLL. On that page, copy the 1-25 rows of four columns each, without the column headings. That is the text I used. It is the third column of numbers, the won-loss records (e.g. 8-1, 7-0, 5-2) that kept changing into dates for me, until I found my solution mentioned above.

---

FOR NOVICES: Just in case you're not following how to "change the cell number formatting to text" in Calc here are the steps:

1) Select the cell(s) (or row(s) or column(s), or the entire sheet) where you don't what the date reformatting to happen

2) In the Format menu, select menu item Cells...

=> you'll see the Format Cells dialog box, and the Numbers tab should already be highlighted for you (if not you'll need to select Numbers)

3) In the "Category" subwindow, scroll down to the end and select Text

4) Hit OK

Now those cells should not reformat dates when you copy-and-paste (unless you've got some kind of rich text that doesn't work - as mentioned above.)

[Villeroy]

Go to d2football dot com, and in the left column, click on TOP 25 POLL.

Why that complicated? SImply paste-special plain text, turn off "special number detection" or mark the 3rd column as text.
Copied from spreadsheet:

Code: Select all

1	Delta State 	8-1 	1
2	Pittsburg State 	8-0 	4
3	Bloomsburg 	8-0 	3
4	Northwest Missouri 	7-1 	7
5	North Alabama 	6-1 	5
6	Minnesota Duluth 	7-1 	8
7	CSU-Pueblo 	8-0 	9
8	St. Cloud 	7-1 	11
9	Midwestern State 	7-0 	13
10	Wayne State (Mich.) 	7-1 	12
11	Nebraska-Kearney 	7-1 	14
12	Winston-Salem 	8-0 	15
13	Abilene Christian 	5-2 	2
14	Valdosta State 	6-2 	10
15	Washburn 	7-1 	6
16	West Texas A&M 	5-2 	17
17	Kutztown 	7-1 	18
18	New Haven 	7-1 	19
19	West Virginia Wesleyan 	8-0 	22
20	Albany State 	6-2 	21
21	California 	6-2 	23
22	Slippery Rock 	7-1 	NR
23	Central Missouri 	5-3 	20
24	Edinboro 	6-2 	NR
25	Humboldt State 	6-1 	16

[JohnnyHands]

Thanks Villeroy for pointing me in the direction of Paste Special...

Here's a fix for Mac OS X that's not quite as easy as you suggest (e.g. where do you turn off "special number detection?) Also, I had to take another step to save the tab-delimited columns.

1) Copy the text you want to paste into Calc (I used the d2football data I mentioned two posts above this one )

2) For the data I'm using, it's column C that I don't want turned into dates, so select column C and then use menu item Format -> Cells..., then the Numbers tab, Category list, scroll down and select Text, then hit OK. This will set up Column C to not reformat dates.

2b) click the upper left cell of the cells where you want the data to go (for me it was cell A1)

3) Use menu item Edit -> Paste Special...

==>> The Paste Special window appears

4) In the selection section make sure "unformatted text" is selected, and hit OK

==> the Text Import window appears. Note in the Import section the Character Set is already "Unicode" which I left that way

5) Still in the Text Import window, to keep the tab-delimitation, in the Separator Option/Separated By section, check the Tab checkbox (this keeps the column width as four, as I want, rather than putting all columns in a row in one cell, which will happen if you skip this step.)

6) Hit OK

=====>>>>> You've got the 25 rows and 4 columns with column C NOT re-formatted to dates (instead in column C, you've got, e.g., 8-1, 7-0, 5-2, just as the source data was displayed.

SUCCESS!!

[lily_e]

Uncheck the option "Number Recognition" -magically works!!

[timothyso]

Those of you replying that 'it is interpreting exactly what it sees' are failing to realize that
ALL inputs are either TEXT or NUMERIC and you assume that OO is smart enough to see the difference.

3 cases in point.

course area reg
100 Math 5/12

version 5.1.1

serial number 140-34105.15

NOW: ASSUMING that each space represents, say a tab then pasting these texts into OO calc is HELL... fact...

EVEN when one formats the columns that are coming into text then the MACHINATION to convert anything to what you want is insane.

THE question is and will ALWAYS be, can a specific format area be created and ALL information be left in that form when pasting.

That way my 5.1.1 my 140-34105.15 and my 5/12 will not be corrupted by OO.

Something as simple as ALL cells are text.

Then if we want to say process the 5/12 by inserting two columns to the right of it and getting a numeric 5 and a numeric 12 out of it can be done.

SIMPLY PUT It is nearly impossible to force OOcalc to leave my TEXT alone.

[Villeroy]

A text editor, a database, enter text, paste text, import text.

[acknak]

...SIMPLY PUT It is nearly impossible to force OOcalc to leave my TEXT alone.

Hmm...

1) Copy the sample text
2) Paste Special into Calc, as Unformatted text

In the text import window:
3) Separated by: SPACE
4) Select all columns in the data grid (click in the upper left corner)
5) Choose column type: TEXT
6) OK

Does that not do what you want?

[gp212]

Sorry to bump an old thread.

This is from the help menu.

Automatic Conversion to Date Format
OpenOffice.org Calc automatically converts certain entries to dates. For example, the entry 1.1 may be interpreted as January 1 of the current year, according to the locale settings of your operating system, and then displayed according to the date format applied to the cell.
To ensure that an entry is interpreted as text, add an apostrophe at the beginning of the entry. The apostrophe is not displayed in the cell.

[kingfisher]

What is the problem?

[ASSH]

To expedite future searches for information on this application flaw in this forum thread, I would like to request that the initial post in this topic be amended to state something along the lines of:

The current versions of OpenOffice Calc (up to 3.4.0 at this time) will automatically modify input such as "5/3", "3.4.1", or "12-1" in to dates (formatting and modifying the input information according to your current locale settings). This "feature/bug" can not be disabled and you must contact the official OpenOffice development team to encourage a change to this program behaviour for future versions.

Thankfully, preventative actions can be taken to avoid loss of data when manually inputting or copy-and-pasting numerical text like the above examples. Note: The below solutions are for users with the Windows series Operating Systems. Mac users should have similar solutions available to them.
Method 1 - Pro: Useful for both manual input and copy-and-paste. Con: Requires foresight.
1) Select the cells you wish to input the non-date numeric data
2) Right-click and select "Format Cells..."
3) Select "Numbers" Tab
4) Under "Category" select "Text" (there should be only one option in "Format" at this time: "@")
5) Press "OK"
Following these steps will prevent data in the modified cells from being automatically interpretted as Dates by OpenOffice Calc.

Method 2 - Pro: Useful for manual input. Cons: Not possible for copy-and-paste. Requires extra input per entry.
Simply lead all input with the apostrophe / single-quotation mark character. '
Examples:
'3.4.1
'2-5
'5/2
The apostrophe will notify OpenOffice Calc to treat the cell as text. If you forget to input the apostrophe, press Ctrl+Z to Undo, rather than attempt to input the information again. Once a cell has had numerical data input in to it which OpenOffice assumes to be a date, the cell will continue to be treated as a date unless you Undo or manually reconfigure the cell using the "Format Cell..." Method 1.

Method 3 - Pro: useful for copy-and-paste. Con: Not possible for manual input. Requires preliminary work in another application.
1) Compose your data in a pure text application such as NotePad (all versions of Windows from 3.1 on have NotePad). Use the Tab key on your keyboard to simulate seperated cells across Columns, and Enter/Return to simulate seperated cells down Rows
2) Highlight the text
3) Select "Copy" (default shortcut: Ctrl+C)
4) In OpenOffice Calc: Right-click in the desired cell that will be the start (upper-left corner) of your range of cells and select "Paste Special..."
5) A dialogue box labelled "Paste Special" MAY appear with "Unknown source" and "Unformatted text" in it. You can ignore this and simply press "OK"
6) A dialogue box labelled "Text Import" WILL appear. As long as these settings are left as their Defaults, you simply need to press "OK"
Following these steps should reproduce your numerical data in the spreadsheet without any "Autocorrect" or automatic modifications to the formatting.

[Villeroy]

... Lots of reasonable stuff ...

This is the same behaviour in all spreadsheet programs (including Excel since the early 90ies) and it has been discussed in many thousands topics during the past 2 decades.

The problem is not the way how spreadsheets evaluate text input from keyboards, files and clipboards. The problem is that 99% of todays spreadsheet users are unwilling to learn any concept that goes beyond what they use to call "intuitive" (I*d call it "arbitrary").
Another problem which makes the given concept really difficult to understand is number format "@" (text). It is no number format at all. A difference in data types has nothing to do with formatting (the term "text formatting" is extremely misleading). "@" is a switch to turn off all input evaluation. "@" shows any existing number in the general format. Like all the other number formats and all the other formatting attributes (fonts, colors, orientation, borders) "@" does not change a single value.

LibreOffice 3.6 messed around with input evaluation. Now I can not enter any dates on the num-pad of my keyboard with German locale setting and LibO 3.6. With English locale settings I can not enter recent dates as quick as I used to be able in the past 2 decades. Dumbing down the product towards the dumbest assumable user does not pay off.

[ASSH]

Villeroy . . . I understand that you are a volunteer at this site, but the attitude you've expressed in this thread is not congruent with this forum's function as a site to assist the OpenOffice userbase with their issues with the OpenOffice program suite. Statements such as "The problem is that 99% of todays spreadsheet users are unwilling to learn any concept that goes beyond what they use to call "intuitive" (I*d call it "arbitrary")" are easily construed as inflammatory and not helpful in answering users' queries about the software behaviour. Additionally "This is the same behaviour in all spreadsheet programs (including Excel since the early 90ies) and it has been discussed in many thousands topics during the past 2 decades" should not change the fact that unless a precise duplication of a user inquiry has been posted, each thread should be treated as "fresh" and the complaint/issue given full and unbiased attention. Even in the cases of a precise duplicate thread-starter post, a polite referral and link to the existing thread is certainly more courteous than passively berating the afflicted users.

I realize calling you out on this publicly is potentially an error on my part, but I would like to ask you to give serious consideration to the language you use and the expression of personal opinions in threads meant to solve problems. I haven't reviewed your posts in all other threads here, but in this particular thread and one other I noted a definite "attitude" present in most posts authored by you. I bring this up in hopes of improvement for future users' experiences while searching for answers on this site.

Best regards.

[Villeroy]

You registered with this forum to write a tutorial on text input? We have one already.
Call me an arrogant asshole if you like. I don't care.

[NaturaLee]

I'm a novice on OO. Could not figure out how to stop an entry like "09/18" stop converting to "09/18/14" - but got it now: "TABLE" - "Select - Columns". Back to "TABLE", "Number Format", scroll down to "Text", hit OK and you're done.

[Villeroy]

I'm a novice on OO. Could not figure out how to stop an entry like "09/18" stop converting to "09/18/14" - but got it now: "TABLE" - "Select - Columns". Back to "TABLE", "Number Format", scroll down to "Text", hit OK and you're done.

Well, this is the Calc forum and I guess you are talking about Writer. Anyway, to summarize most of this topic:

Number format "Text" (no number at all) takes all input as literal string.

Format as text. This is the switch to turn off all evaluation of the selected cell(s). Then enter your strings into the prepared text cells.

As Villroy says "Format as text."

You need to format the cells before you enter any data.

As was stated above, format the cells as text before you type into them:

Highlight the cells (or the entire sheet if you want) and go to Format > Cells > Numbers Tab > select Text from the Category drop down menu.

Did you format the cells before you entered the data? It works fine for me.

FOR NOVICES: Just in case you're not following how to "change the cell number formatting to text" in Calc here are the steps:

1) Select the cell(s) (or row(s) or column(s), or the entire sheet) where you don't what the date reformatting to happen

2) In the Format menu, select menu item Cells...

=> you'll see the Format Cells dialog box, and the Numbers tab should already be highlighted for you (if not you'll need to select Numbers)

3) In the "Category" subwindow, scroll down to the end and select Text

4) Hit OK

1) Select the cells you wish to input the non-date numeric data
2) Right-click and select "Format Cells..."
3) Select "Numbers" Tab
4) Under "Category" select "Text" (there should be only one option in "Format" at this time: "@")
5) Press "OK"

Yes, there is a similar command in Writer menu:Table>Format... number format category "Text" which does the same for selected cells and in Writer there is table-right-click>NumberRecognition which turns on and off any number recognition in tables.

When importing/pasting any text, it is important to not ignore options, including the help button on the import dialog.