when i past -123 into a cell, it turns into: '-123 why?
it treats it as text and breaks everything.
edit:
oh, it is the decimal separators. The place i’m copying from uses 1.234,12 and my system expects 1,234.12
Worked around with: find+replace ALL . → (nothing), then , → .
Now all cells are numbers.
Q: Is there a way to smartly convert to right decimals when pasting that i’m missing?
Pre-format the target cells to the locale using the conventions of the data you copy?
Use text import dialog, where you can select the incoming data locale?
i was thinking more of a way to detect #+([\.,])## and convert the match group to the target locale decimal. since for this work i’m copying lose numbers from several places
…my reasoning being: if I’m paying attention to this, i can just quickly fix the commas/points. but i don’t want to have to pay attention to something that is so trivial. makes sense?
(off topic, from having looked for the source at maybe contributing something like this… now i wonder how many contributors libreoffice loses for not moving to git already)
LOL - we use git from day 1. But do you think that “git” is limited to github? We use Google’s Gerrit review system.
No. What is 10.000 in source? Is it ten, or ten thousand? You as a human will be able to tell from some external data; but not the program. Number formats are not trivial.
oh didn’t know gerrit was git now. only used rietveld and Mondrian at previous job.
i disagree with the difficulty figuring out decimal or not. but maybe I’m overlooking some edge case. but even with a few edge cases, there will be only two options to choose from. showing a two option dialog is better than failing 100% of the time and shoving text into a number cell i guess.
anyway, will start with an extension for now
If you are going to show a dialog anyway, what’s wrong with the existing Text Import dialog (which gives you infinitely more flexibility)?
we might be talking about two different things…
but if it is the text import i’m thinking, I cannot see how “alt+tab, copy something, alt+tab paste something in calc”
is equivalent to “create a csv document, import it in calc. copy from new sheet. back to original sheet. paste”
edit: ok, just learned i can reach it via “data > text to column” (i would never have guessed by the menu labels)
but the dialog helps in nothing to convert string to a number:
Pasting as plain text (Ctrl+Alt+Shift+V) allows you to have it in your original procedure. With an option to select the locale of the original data.
Text to Columns does not allow to select locale - because you can apply locale to individual cells (or rows) before the function.
There is FAQ.
And in general, I suggest to learn the basics of the software you are using, prior to starting creation of extensions…
I don’t think the manual will tell us more than we already explored here, which is being very helpful in mapping all the idiosyncrasies and limitations. Appreciate the help. But even the FAQ already opens with the desperate find+replace I did early on.
summing up what worked and what was worse:
preemptively setting the locale on the cells: worse. it does nothing for numbers. You can see my screen show is locale=danish (which uses comma) yet row 1 is seem as text (with the comma), and row 2 is seeing as number (with a dot). completely ignoring the locale. And even worse, now text with comma (the danish locale of the cell) will NOT show as '1,01 but as 1,01 (i.e. no indication it is a string) but will still behave as Text! (i.e. not being counted on sum() etc!)
NOT changing the locale, and using special paste: It does work if i explicitly change the locale to danish only when pasting with ctrl+shit+alt+v… but it changes the locale currency, which will make everything even more maddening in the long run.
(don’t know if danish locale have some oddity causing these things… it is just the locale i usually go to when i need ISO8601 dates or commas on currencies)
I think the extension is the only option to have a sheet with all sane single-decimal separator but conveniently pasting/importing from random sources.
edit:
after cleaning the manual formatting, the ctrl+shift+alt+v does work perfectly (import from locale X but into locale default!)… but ONLY if you copied more than X lines… grrrrr… why??? if i have only a single value copied it just paste it there without a dialog or transformation.
PS: anyone reading this in the future, you need “[x] special numbers” in that dialog, to be able to past negative ones.
Click the help button on the import dialog.
FInd&replace tries to fix wrongly imported data. It is way easier and more reliable to import data correctly.
what if i don’t get the dialog? 
If the point is presented in CALC as a block of thousands, this is neither typographically correct nor mathematically correct!
Alternatively, the number 10000, ten thousand, can be formatted with #" "##0 to display a maximum of 2× blocks of 3. This is the only way to determine whether the decimal is English with a point or German with a comma.
Something you might like to play around with:
000_LO-CALC_text string to numerals and operators_110116.ods (30.4 KB)
Where do you copy from?
OK, you don’t get an import dialog when you paste a single value from a plain text source.
The attached document shows what happens when I paste 1.234,56 from a plain text editor to an English cell and to a German cell. Works as expected. No automatism, no voodoo. Paste to a formatted cell and if you are done, you may switch to another format.
ask123304.ods (15.4 KB)
