0

This question already has an answer here:

I am trying to edit a csv file in excel, but am running into issues with its supposedly-helpful automatic format detection, and I don't know how to turn it off.

I have a field with an original value "28:55". Excel converts this immediately on loading the file into "28:55:00", thinking it needs to be in hh:mm:ss format. When I try to manually convert the format back to a string, it inexplicably transforms into "1.204861111".

How do I turn off this automatic data conversion when loading a file? I haven't been able to find anything helpful in the excel settings.

[edit]: This is Excel 2010. All online resources I have looked at just tell you how to stop automatic conversion once a file is loaded, not how to stop the conversion that happens during Excel's initial reading of the data.

marked as duplicate by Karan, Community Jun 12 '15 at 14:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Welcome to Super User. Please tell us what version of Excel you are using and what you research you have done in order to resolve this. This information will help us give you an accurate, applicable answer. – CharlieRB Jun 11 '15 at 19:47
  • If you just want to be able to process these files and are not committed to Excel you should know that the open-source office suites LibreOffice and OpenOffice both prompt when opening csv/tsv to allow you to control all aspects of how the file is processed. You can set all columns to just be the type Text and do no automatic formatting. – SleighBoy Jun 11 '15 at 21:09
1

Instead of opening the file normally, try importing it. In the data tab of the ribbon, click From Text in the Get External Data group. In the final screen of the text import wizard there's an option for the data format, pick Text.

If that doesn't work, use this formula to convert back to the format you want, then paste values:

=TEXT(A1,"[h]:mm")
-1

Not sure what version you are using, but a quick search resulted in this from Microsoft...

Turn on or off automatic formatting

When you set options on the AutoFormat As You Type tab, Excel can automatically format text as you are typing your worksheet.

  • Click the File tab.
  • Under Help, click Options.
  • Click Proofing.
  • Under AutoCorrect Options, click AutoCorrect Options.
  • Click the AutoFormat As You Type tab.
  • Select or clear the check boxes for the options that you want to enable or disable. For more information on the options, see the
    section below.

You may have to turn these features off from a different file, then open the CSV in question.

Source

  • 1
    That only solves manual-entry autocorrect (and besides, I can't see my particular case in the check-boxes). The issue is that Excel does the transformation when the file is loaded. – Walker Jun 11 '15 at 19:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.