[Solved] Prevent Calc from considering pasted value as text?
[Solved] Prevent Calc from considering pasted value as text?
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?
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?
Last edited by MrProgrammer on Sun Jan 03, 2021 9:30 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
Re: How to prevent Calc from considering pasted value as tex
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.
Last edited by Villeroy on Sun Oct 27, 2013 12:50 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to prevent Calc from considering pasted value as tex
None of the above.Horus wrote:I don't know if I'm doing things incorrectly or if I'm hitting a bug. ... Why is Calc so dumb?
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
AOO4/LO5 • Linux • Fedora 23
Re: How to prevent Calc from considering pasted value as tex
Hallo
Karolus
you don't need c&p to Texteditor an back, for this simple replace-procedure!!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
Karolus
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: How to prevent Calc from considering pasted value as tex
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.Villeroy wrote: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.
Don't tell me I have make a webpage first in order to paste!
Re: How to prevent Calc from considering pasted value as tex
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.acknak wrote:None of the above.Horus wrote:I don't know if I'm doing things incorrectly or if I'm hitting a bug. ... Why is Calc so dumb?
Calc, as compared to Excel, refuses to assume anything--and possibly make a mistake--and instead expects you to tell it what to do.
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.
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.acknak wrote: 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 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.
Re: How to prevent Calc from considering pasted value as tex
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.karolus wrote:you don't need c&p to Texteditor an back, for this simple replace-procedure!!
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!
Re: How to prevent Calc from considering pasted value as tex
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to prevent Calc from considering pasted value as tex
OK, this is better. Thanks. I didnt see it as this copy-paste, format-choosing, paste-special-dialog matters were getting on my nerve.Villeroy wrote: [X] Current Selection Only
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.
Re: How to prevent Calc from considering pasted value as tex
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.Horus wrote:Villeroy wrote: There's another reason why I chose to use notepad: to get rid of all formats in order to process pure data.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to prevent Calc from considering pasted value as tex
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.Horus wrote:... 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.
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
Copy and paste the following date into Calc and see what date you get in the cell:Horus wrote: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.
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.
AOO4/LO5 • Linux • Fedora 23
Re: How to prevent Calc from considering pasted value as tex
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,
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,
Libre Office 4.1.4.2 on Fedora 19
Re: How to prevent Calc from considering pasted value as tex
Horus wrote: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]acknak wrote:None of the above.Horus wrote:I don't know if I'm doing things incorrectly or if I'm hitting a bug. ... Why is Calc so dumb?
Calc, as compared to Excel, refuses to assume anything--and possibly make a mistake--and instead expects you to tell it what to do.
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.
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.acknak wrote: 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 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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: How to prevent Calc from considering pasted value as tex
1/2/2014;1,234IMHO, 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.
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.
Well, the people who wrote this program are way smarter than you and me.For me, together with the first part of "refusing to assume bla bla bla", Calc is dumb.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to prevent Calc from considering pasted value as tex
IF you add a blank line the text import window will appear.acknak wrote: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
AOO 4.1.15 & LO 24.2.2 on Windows 10
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
Re: How to prevent Calc from considering pasted value as tex
Only quickly logging in to thank acknak. Always providing spot-on answers here!acknak wrote: 3) Click the "detect special numbers" option
Click OK
Yes, embarassingly I missed that tickbox too, so fast I work...
Ubuntu / Win10 /Virtualbox