I want to copy text from Notepad to OO Spreadsheet that has columns with number with trailing zeros.
Eg;
457.000 becomes 457
3.000 becomes 3
What puzzles me is, when I do the 'paste', the pop-up box in the 'Field' window does correctly show the full numbers with the decimal point and all the trailing zeros. But, when I click OK, any number that has a decimal point and trailing zeros becomes a full number. Look at the attachment under any of the columns with a decimal point, they are formatted correctly until I actually do the paste. The original formatting is necessary as this goes into a piece of test equipment that also editing these files in Notepad, but the changes require repetitive changes to an entire column to the same number which can only be done one at a time which is really tedious (over 40 entries).
Specifically what I need to change is that 2nd last column; from 0.000 to -3.000 as I already did in the 1st row.
Copy text from Notepad to Spreadsheet w/ trailing zeros.
-
- Posts: 63
- Joined: Fri Feb 16, 2018 6:18 pm
- Location: New York State, the Empire State
Copy text from Notepad to Spreadsheet w/ trailing zeros.
Last edited by videobruce on Fri Feb 16, 2018 7:03 pm, edited 2 times in total.
OpenOffice v4.13
Win 7 & XP Pro
Win 7 & XP Pro
-
- Posts: 63
- Joined: Fri Feb 16, 2018 6:18 pm
- Location: New York State, the Empire State
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
I guess if the text Import screen showed the same as the end result it would be different.
I looked thru the Settings, but couldn't find anything that even remotely looked like it would fix the problem.
90% of those I don't change anyway. Here is what I mean after the copy;
I looked thru the Settings, but couldn't find anything that even remotely looked like it would fix the problem.
90% of those I don't change anyway. Here is what I mean after the copy;
OpenOffice v4.13
Win 7 & XP Pro
Win 7 & XP Pro
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
Look at the formatting code for the destination cells.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
The import converts strings of digits and points to spreadsheet values and this is the correct result. You get the same numbers when you type these strings on the keyboard into blank, unformatted spreadsheet cells. No auto-formatting takes place. You get the correct raw values.
If I would import the same file into my Office (Excel or Calc) I would get German numbers with decimal commas instead of decimal points but still the same numbers and that is the only important thing.
[Tutorial] Ten concepts that every Calc user should know
If I would import the same file into my Office (Excel or Calc) I would get German numbers with decimal commas instead of decimal points but still the same numbers and that is the only important thing.
[Tutorial] Ten concepts that every Calc user should know
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
-
- Posts: 63
- Joined: Fri Feb 16, 2018 6:18 pm
- Location: New York State, the Empire State
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
I realize from a 'math' standpoint typing "2" is the same as typing "2.000", they both are the same value. I would then assume typing "2.001" would get copied as is?
Is there a way around that?
Is there a way around that?
OpenOffice v4.13
Win 7 & XP Pro
Win 7 & XP Pro
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
Once the data has been pasted, format the target cells as you wish them to appear. Note in the image below that the content of A1 is -3 but is displayed as -3.000 Similarly A2 contains 23.7RoryOF wrote:Look at the formatting code for the destination cells.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
-
- Posts: 63
- Joined: Fri Feb 16, 2018 6:18 pm
- Location: New York State, the Empire State
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
Interesting.
Most of the columns are affected, that would restore all of these? Is there a way to do that before the actual 'paste'?
Most of the columns are affected, that would restore all of these? Is there a way to do that before the actual 'paste'?
OpenOffice v4.13
Win 7 & XP Pro
Win 7 & XP Pro
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
I don't have a set of data to experiment with, but you could try setting the format before pasting; you will need to have some idea of the column structure into which you are pasting the data.
If you can import the data from a csv file, this post may help with pre-formatting
If you can import the data from a csv file, this post may help with pre-formatting
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.
Prepare a template where the right columns are formatted in the right ways, preferably by means of cell styles.
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