How to paste numbers as type numeric reliably?

DavidLedger · June 28, 2021, 3:50pm

If I copy a rectangular array of cells that all contain numbers from one Calc doc and paste it into another, a few cells are pasted with type=String ( TYPE() gives 2 ) whereas most get pasted as type Number. Those pasted as String of course don’t get included in SUM(…) and the like.

Why is this and can I prevent it?

In the source Calc doc the columns containing the ‘copy’ cells are Formatted with ‘Number Format’ set to “Number”.

( If I export the destination doc as .xls I can see the bad cells have ‘’ whereas the good ones have ‘’.)

David

mikekaganski · June 28, 2021, 3:57pm

… and what is the result of TYPE(<some cell>) in the source document? The cell format is irrelevant here.

DavidLedger · June 28, 2021, 4:11pm

In the source document the TYPEs of all the numbers are 1, Numeric. There are a number of empty cells in the source, and they have TYPE 2, Text. When those empty cells get pasted in they get TYPE Numeric.

David

mikekaganski · June 28, 2021, 4:21pm

Are you pasting normally, or maybe as plain text?

erAck · June 28, 2021, 5:11pm

Many times with anything clipboard unexpected types related some clipboard destroyer (aka “manager”) is running. Make sure you can rule that out. (though it’s odd that some cells are pasted numeric and some not).

Also, when using Paste Special without formats, make sure destination cells aren’t formatted to type Text already.

DavidLedger · June 28, 2021, 5:12pm

I’m pasting normally.
Ah-ha. If I paste numeric only, one column for numbers remains blank. If I paste only text, those numbers go in (but obviously the others don’t).

David

DavidLedger · June 28, 2021, 5:45pm

I’ not running any clipboard utility. I’m running Ubuntu with basic clipboard apart from a link into the X-windows clipboard.

The numeric recipient columns are set to ‘Number Format’ = “Number” before the paste.

keme · June 28, 2021, 6:28pm

If one column is skipped when you paste numbers only, then that column is being transferred as text by the clipboard. The only cause I have seen for this is when the source is text, which you have clearly determined is not the case this time.

Does the column have a formatting distinctly different from the others? It may be that your clipboard prioritizes the visual presentation over numeric content. If that is e.g. the only column formatted for currency, or the only place where thousands separators display, this may be a clue.

DavidLedger · June 28, 2021, 8:22pm

I’ve been setting the format by selecting all the (adjacent) numeric columns and setting them together. Interestingly when I return to the Format->Number Format menu entry after setting it, it doesn’t always indicate a setting.

DavidLedger · June 29, 2021, 8:50am

(Tuesday AM in UK) Now concentrating on the source document as it looks like the Copy is loading the clipboard with one numeric column described as Text.

I cannot set any columns as Number without the Thousands Separator being on. Select Number and the Thousands Separator turns on too. Deselect Thousands Separator and Number turns off leaving no Format selected. Is this normal?

This source document is an imported .csv file. Once imported, can this make a difference. I would expect no difference between the internal representation of an imported .csv file and that of a .ods file. The .csv file is generated by a script from a bank statement download. It’s csv format is extremely basic. The destination file receives Date, Description, In, Out and Balance columns and has more columns over to the right that pull out Ins and Outs according to known text in the Description. These are then totalled normally.

mikekaganski · June 29, 2021, 8:56am

You are talking about formatting in several places, and I want to ask you once again: did you positively check that inserting a formula in your source file, like =TYPE(<problematic cell>), referring to the cell in the source file (that arrives as text when pasted to destination), really gives you 1? Because, as I mentioned, the cell format is irrelevant for the question, and I strongly suspect that you already have text in your source cells (maybe just because you use wrong import settings for the CSV).

Also using View|Value Highlighting may be a better/easier option to check.

DavidLedger · June 29, 2021, 4:27pm

I have used the TYPE() function in the source document, the first of the three columns that should be numeric has type Text for the non-empty cells and Number for the empty cells.

I did look at the import csv settings and by default the Column Type for all columns is ‘Standard’. There is no Numeric option. Options are Standard, Text, Date(DMY), Date(MDY), Date(YMD), US English and Hide. I’m importing using a pipe (|) separator because that is what the generating script uses.

— Solution —
Checking an octal dump of the file downloaded from the bank I discovered that the amount field (which I split into two columns depending on sign) had a trailing tab character attached to it. The balance field didn’t. Changing the sign of the amount and moving it into an ‘Out’ column in my script was enough to remove the tab from negative values. It remained on the positive amounts that were put into the ‘In’ column. Removing non-numeric chars from the numeric fields fixed it. Many thanks to all.