{"id":4967,"date":"2015-03-21T13:20:25","date_gmt":"2015-03-21T12:20:25","guid":{"rendered":"http:\/\/emilkirkegaard.dk\/en\/?p=4967"},"modified":"2015-03-21T13:20:52","modified_gmt":"2015-03-21T12:20:52","slug":"pasting-numbers-with-leading-zeros-into-spreadsheet-without-losing-the-zeros","status":"publish","type":"post","link":"https:\/\/emilkirkegaard.dk\/en\/2015\/03\/pasting-numbers-with-leading-zeros-into-spreadsheet-without-losing-the-zeros\/","title":{"rendered":"Pasting numbers with leading zeros into spreadsheet without losing the zeros"},"content":{"rendered":"

I found some interesting data in a PDF. I want to get it into R for statistical analysis. However, the author has removed the decimals. So, when I try to paste the numbers, spreadsheet software removes the leading zeros which it considers redundant. Googling this issue reveals that a<\/a> number<\/a> of other people have a similar issue and that they haven’t found a good solution. For handling just a few cells, one can insert ‘ in front which solves the issue. This doesn’t work for pasting data.<\/p>\n

Here’s what I did:<\/p>\n

    \n
  1. Copy the table to a simple text editor, I use notepad++<\/a>. Your editor must support regex.<\/li>\n
  2. Use regex<\/a> to insert the decimal separator (, or . depending on your preference\/language).<\/li>\n
  3. Copy the table to the spreadsheet.<\/li>\n<\/ol>\n

    This works.<\/p>\n

    In my case, I had a table that looks like this:<\/p>\n

    \"table<\/a><\/p>\n

    First, I OCR’d the table with ABBYY FineReader<\/a>. Then I fixed any possible OCR errors (there was 1). Then I copied it to notepad++. Then I used search replace with the following regex parameters: search=”(\\d\\d\\d)”, replace=”.\\1″. In normal language this means: find any sequence of 3 digits in the text. Replace this by a dot followed by whatever you found. So this finds e.g. “011” and replaces with “.011”.<\/p>\n","protected":false},"excerpt":{"rendered":"

    I found some interesting data in a PDF. I want to get it into R for statistical analysis. However, the author has removed the decimals. So, when I try to paste the numbers, spreadsheet software removes the leading zeros which it considers redundant. Googling this issue reveals that a number of other people have a […]<\/p>\n","protected":false},"author":17,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2089],"tags":[2122,2121],"class_list":["post-4967","post","type-post","status-publish","format-standard","hentry","category-programming","tag-regex","tag-spreadsheet","entry"],"_links":{"self":[{"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/posts\/4967","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/comments?post=4967"}],"version-history":[{"count":1,"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/posts\/4967\/revisions"}],"predecessor-version":[{"id":4969,"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/posts\/4967\/revisions\/4969"}],"wp:attachment":[{"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/media?parent=4967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/categories?post=4967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/emilkirkegaard.dk\/en\/wp-json\/wp\/v2\/tags?post=4967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}