2

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.

CharlieRB
  • 23,021
  • 6
  • 60
  • 107
Walker
  • 121

2 Answers2

2

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")
Kyle
  • 2,436
-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

CharlieRB
  • 23,021
  • 6
  • 60
  • 107