2

This is the most ridiculous thing i've encountered.. I've imported a csv, I end up with nice neat clean rows of numbers, no formatting, nothing weird, no " or ' left over, just numbers. When I go to change the format to currency, instead of doing that it leaves it the same at a glance but looking at the formula it adds a little ' at the beginning..m So instead of -145 it becomes '-145

Same thing happens if I clear direct formatting. Nothing changes visually but now all cells have this damned ' at the start of the formula. This thing is preventing sum from working, it's preventing everything and I have no idea how to get rid of it.

If I select a cell and try to paste it into another sheet the same behavior happens. If I paste special and paste numbers only then nothing pastes, it's completely blank.

Anyone have any idea as to what is going on here? Those 's are not present in the CSV fiel at all until any of the above is attempted

PS - importing the CSV into google drive and opening it with google docs does not result in this behavior

Sample line in the Csv file:

"1/1/2012","18:22:19","PST","VENDOR NAME","Shopping Cart Payment Sent","Completed","USD","-33.25","0.00","-33.25","myemail@email.com","theiremail‌​@email.com","confirmation####","Non-U.S. - Verified","","Shopping Cart","","0.00","","0.00","","","","","","","","","","","","#######","","","#,##‌​#.##","","","","","","","", –
Improve this question
  • 1
    ' makes the content explicitly text. can you post some rows from your csv? maybe there are some leftover characters that make the value interpreted as text, even though they are then stripped from the imported text? – Silly Freak Mar 6 '14 at 17:57
  • Sure, here's a censored row, only thing changed are names and numbers "1/1/2012","18:22:19","PST","VENDOR NAME","Shopping Cart Payment Sent","Completed","USD","-33.25","0.00","-33.25","myemail@email.com","theiremail@email.com","confirmation####","Non-U.S. - Verified","","Shopping Cart","","0.00","","0.00","","","","","","","","","","","","#######","","","#,###.##","","","","","","","", – unc0nnected Mar 6 '14 at 18:02
  • it would be easier to use if you added that to your original message in a code block, but that works too. I had to replace the commas for my locale, but apart from that it worked. you imported the csv ny opening it with libreoffice, or some other way? – Silly Freak Mar 6 '14 at 18:16
  • 1
    The text import dialog has a checkbox "Quoted field as text". when I tried it, that checkbox did not make any difference, but it really sounds like that would give you the results you're seeing. – Silly Freak Mar 6 '14 at 18:19
  • I can reproduce the issue only partially - changing the format to currency works fine for me with the test data, but i had to explicitly set the language to English (US) in the csv import options. Here's a solution to remove the protecting singe quotes: superuser.com/a/394170/84724 – tohuwawohu Mar 10 '14 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.