[Solved] Prevent Calc from considering pasted value as text?

[Horus]

I don't know if I'm doing things incorrectly or if I'm hitting a bug.

I have a list of date to enter into Calc. In DD/MM/YYYY format, they look like this:
01/01/2003
16/01/2003
01/02/2003
16/02/2003
...
16/12/2003

And I have to do this for several years. So in order to avoid error and not wanting to repeat things, I copied the list of date of 2003 into a text editor, replace all 2003 to 2002, select all those dates and paste back in Calc. But Calc keeps on considering them as text! That means every value is prefixed with a '
'01/01/2002
'16/01/2002
'01/02/2002

But if I select only one date (ie one line) and paste it, this time the text is considered as date value!

When I'm doing the same thing (24 lines of text) in Excel, pasted text are considered as value defined by the cells' formats. Why is Calc so dumb?

[Villeroy]

Works for me. I copy your dates from this page, paste into Calc and get correct date values where I can replace the 4-digit year portion.

[acknak]

I don't know if I'm doing things incorrectly or if I'm hitting a bug. ... Why is Calc so dumb?

None of the above.

Calc, as compared to Excel, refuses to assume anything--and possibly make a mistake--and instead expects you to tell it what to do.

When you paste your text, you should get the text import dialog window. In that window, you need to ...

1) make sure the language setting (at the top) matches your incoming text.

2) select the proper delimiter character--in your case this won't matter since you only have one column of data

3) Click the "detect special numbers" option

Click OK

[karolus]

Hallo

I copied the list of date of 2003 into a text editor, replace all 2003 to 2002, select all those dates and paste back in Calc

you don't need c&p to Texteditor an back, for this simple replace-procedure!!

Karolus

[Horus]

Works for me. I copy your dates from this page, paste into Calc and get correct date values where I can replace the 4-digit year portion.

I think that's the problem/difference. Indeed, if I paste them directly from this page, they are accepted as date without that dumb dialog asking what they are. But if I paste them in notepad (or any text editor) first and then copy them to Calc, it's not working.

Don't tell me I have make a webpage first in order to paste!

[Horus]

I don't know if I'm doing things incorrectly or if I'm hitting a bug. ... Why is Calc so dumb?

None of the above.

Calc, as compared to Excel, refuses to assume anything--and possibly make a mistake--and instead expects you to tell it what to do.

I don't agree or share this viewpoint. If the data are plain strings (ie come from text editor), Calc should assume them AS IF they are input from keyboard, just like what Excel is doing.

On the other hand, if Calc really is supposed to refuse to assume anything, then it is not doing correctly either. I can give you two examples to show that it's failing in this job:
1. If I copy just ONE line (including the invisible end-of-line), Calc accepts it. If I copy more than one line, Calc doesn't. So, logically, Calc is not always refusing to assume anything. It's just that its assumptions are flawed.
2. If I copy the date from this page, as suggested by a previous user, Calc accepts without asking "me to tell it what to do". Once again, Calc is failing on its job.

So, at this point, I partially conclude that Calc is dumb.

When you paste your text, you should get the text import dialog window. In that window, you need to ...

1) make sure the language setting (at the top) matches your incoming text.

2) select the proper delimiter character--in your case this won't matter since you only have one column of data

3) Click the "detect special numbers" option

Click OK

For point 1, I had tried several but that didn't change much. IMHO, I don't see why we have to specify the language -- I don't see how that would change anything. I need to see the use case to understand it better.

For point 2, there's none since there's only one column (as confirmed by the "preview" zone at the bottom)

For point 3, this is what I missed. I don't see any *special* numbers without going into the "Help" section.

For me, together with the first part of "refusing to assume bla bla bla", Calc is dumb.

[Horus]

you don't need c&p to Texteditor an back, for this simple replace-procedure!!

Yes, if I simply have a few lines as shown in my example. But things are more complicated in real life than a *simple replace-procedure* can handle.

First, let's say I have to replace 2002 to 2003. I have 2002 *everywhere* in the whole sheet BUT I don't want to replace them all. If I use the "replace all" function, Calc will screw up all my data. Remember that I have to copy some data and replace year only in this piece of them -- "replace all" will inevitably replace the year in the original data, see?

Second, I have more than mere 4 lines to replace. I have tens of lines to replace. I can't use the "replace" function which means I have to click tens of clicks! No way!

[Villeroy]

How to use Find&Replace within selections in all versions of OpenOffice:

Select the cells in question. Multiple selections of dispersed cell ranges are possible (see F1 help)
menu:Find&Replace...
Enter Search and Replace expressions
[More Options...]
[X] Current Selection Only

Now you may hit [Find All...] which selects the matching cells within the previously selected cells. You may also omit this step and continue with [Replace All] which performs all replacements in one go, finally selecting the edited cells.

[Horus]

[X] Current Selection Only

OK, this is better. Thanks. I didnt see it as this copy-paste, format-choosing, paste-special-dialog matters were getting on my nerve.

There's another reason why I chose to use notepad: to get rid of all formats in order to process pure data. The "Paste Speical" dialog is not too intuitive -- or maybe because I'm too used to Excel's convention/behaviour.

[Villeroy]

There's another reason why I chose to use notepad: to get rid of all formats in order to process pure data.

This is a very good reason, indeed. You may also do this with Calc's Find&Replace which supports regular expressions (advanced pattern matching). Find&Replace effectively re-enters the cell content so your formatted text values will be converted to numbers if the replacement is a numeric expression.

For instance, a cell text "Monday, 23/11/2013" will import as text because of the day name, regardless of the fact that 23/11/2013 is not a Monday.
Now you can replace all leading alphabet characters and the comma with the following regular expression: ^[:alpha:]+, and an empty replacement field.
The remaining text " 23/11/2013" will be re-entered into the cell and evaluates to a valid date unless your office uses a USAmerican locale where 23/11/2013 is not a valid date. The locale can be adjusted in the cell formatting dialog before the replacement. It can also be set globally in the language options. Once you have the correct cell values, the locale can be set to anything you like. However, choosing an adequate locale context is vital for any conversion from text to number.
Similar procedures work pretty well with "dirty numerals" of all kind such as strange currency symbols or textual "Yes"/"No" booleans.

[acknak]

... If I copy just ONE line (including the invisible end-of-line), Calc accepts it. If I copy more than one line, Calc doesn't. So, logically, Calc is not always refusing to assume anything. It's just that its assumptions are flawed.

You're right. This exception was added relatively recently, I assume to handle the situation where the user pastes a line of text, not data. That's a common case where the user is not interested at all in importing data, so the text import settings are completely unnecessary and distracting, if not outright confusing.

What's much worse, IMO, is that there is no way to force Calc to treat one line as a text data import, so there's no access at all to the import options if you need them

I don't see why we have to specify the language -- I don't see how that would change anything. I need to see the use case to understand it better.

Copy and paste the following date into Calc and see what date you get in the cell:
11/02/13

If your system language is US English, you got the wrong date: I meant the 11th of February, not the 2nd of November.

Now copy the date above and the empty line below it and paste that. Change the language setting to "English (UK)" and "detect special numbers" ON. Now you get the correct date.

OO is used all over the world, and people on this forum are working in lots of different countries and languages; Calc can't assume that the text being pasted is coming from a source working in US English. You could be pasting something from your browser visiting a website using any date format in the world. You, the user, are responsible for telling Calc how to interpret the data in pasted text.

Normally, you don't have to set the language for pasting text data, but when you're having problems with it, it's something to check. Sometimes people's system, or OO, gets the wrong setting.

The language setting may also prevent dates from being interpreted, if the incoming text uses a different delimiter than the system language. I suppose in your case this clearly isn't the problem, so I could have left that step out. Still, something to be aware of if you run into problems.

[DJJ88]

I'm on Libre Office but it should work the same.

You can copy the date as it is.

01/01/03
01/16/03
02/01/03
02/16/03

Hilight the cells, select Data on the Tool bar then Text to columns.
Hilight again the cells and choose your column type as you see fit.


Cheers,

[jrkrideau]

I don't know if I'm doing things incorrectly or if I'm hitting a bug. ... Why is Calc so dumb?

None of the above.

Calc, as compared to Excel, refuses to assume anything--and possibly make a mistake--and instead expects you to tell it what to do.

I don't agree or share this viewpoint. If the data are plain strings (ie come from text editor), Calc should assume them AS IF they are input from keyboard, just like what Excel is doing. [/q]

And if you have a 15 digit ID number or something from genetics research, you enter it in Calc as text and you're fine. If you enter it in Excel you have the possiblity of rather than having 245345789087565 you end up with something like 2.45*10^12 and your data is irrevocably lost. The latter has happened more than once.

I tend to live in fear that some Excel spreadsheet calculating a drug dose for me will use my telephone number rather than my weight.

When you paste your text, you should get the text import dialog window. In that window, you need to ...

1) make sure the language setting (at the top) matches your incoming text.

2) select the proper delimiter character--in your case this won't matter since you only have one column of data

3) Click the "detect special numbers" option

Click OK

For point 1, I had tried several but that didn't change much. IMHO, I don't see why we have to specify the language -- I don't see how that would change anything. I need to see the use case to understand it better.

For point 2, there's none since there's only one column (as confirmed by the "preview" zone at the bottom)

For point 3, this is what I missed. I don't see any *special* numbers without going into the "Help" section.

For me, together with the first part of "refusing to assume bla bla bla", Calc is dumb.

[Villeroy]

IMHO, I don't see why we have to specify the language -- I don't see how that would change anything. I need to see the use case to understand it better.

1/2/2014;1,234
The computer can not know what this means. Only humans know the meaning of unspecific text data.
Does 1/2/14 refer to 2nd of January (USA) or first of February (rest of world)?
Is 1,234 a number with a thousands separator or with a decimal separator?

With one language setting you specify the overall cultural context for all the numeric text you are going to import. You can still define exceptions for each column in the data preview.
Then you should know what a "special number" is. A special number is a text that contains more than digits and a decimal separator. Figures that include currency symbols, percent symbols, all dates (even with month names), times, scientific numbers, fractions, boolean words "True" and "False". Check the "special numbers" check box if you want all these values to be evaluated in the given cultural context.

For me, together with the first part of "refusing to assume bla bla bla", Calc is dumb.

Well, the people who wrote this program are way smarter than you and me.

[Alex1]

What's much worse, IMO, is that there is no way to force Calc to treat one line as a text data import, so there's no access at all to the import options if you need them

IF you add a blank line the text import window will appear.

[windowshater]

3) Click the "detect special numbers" option
Click OK

Only quickly logging in to thank acknak. Always providing spot-on answers here!

Yes, embarassingly I missed that tickbox too, so fast I work...