Copy text from Notepad to Spreadsheet w/ trailing zeros.

Discuss the spreadsheet application
Post Reply
videobruce
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.

Post by videobruce »

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).
Copy from Notepad to OO Spreadsheet trailing zeros.png
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.
Last edited by videobruce on Fri Feb 16, 2018 7:03 pm, edited 2 times in total.
OpenOffice v4.13
Win 7 & XP Pro
videobruce
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.

Post by videobruce »

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;
Copy to Spreadsheet after copy.png
OpenOffice v4.13
Win 7 & XP Pro
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.

Post by RoryOF »

Look at the formatting code for the destination cells.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.

Post by Villeroy »

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
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
videobruce
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.

Post by videobruce »

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?
OpenOffice v4.13
Win 7 & XP Pro
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.

Post by robleyd »

RoryOF wrote:Look at the formatting code for the destination cells.
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.7
format_decimal.png
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
videobruce
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.

Post by videobruce »

Interesting.
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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.

Post by robleyd »

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
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy text from Notepad to Spreadsheet w/ trailing zeros.

Post by Villeroy »

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
Post Reply