0

I imported a TXT file into Excel.

Some of the entries, in the format 11-2, were treated as dates by Excel.

I did not catch this until it was too late, but Excel also overwrote my original data with the numerical day number. So I no longer have 11-2 in that cell at all, but rather 43041 (the day number of some November 2nd).

I have a great number of these, scattered throughout a 30K row spreadsheet, which uses several different cell formats. All of them were chapter and section numbers in my original TXT file, and I need to recover them.

Suggestions?

Improve this question
6
  • 1
    When you import use the From Text wizard on the data tab. It will let you set that column as text instead of General. As to what to do now, you can use one of these two formula: TEXT(A1,"m-d") or TEXT(A1."d-m") depending on your local settings.
    – Scott Craner
    Sep 11, 2017 at 18:41
  • Oooh ... sorry ... I thought I was clear on this: I found out after it was too late to do that. Many minor corrections were done by hand to this spreadsheet before we found this widespread problem about 20K rows down.
    – boobounder
    Sep 12, 2017 at 16:13
  • Then you will need to use the formula fix in a separate column and paste the values back, making sure to make the cells text.
    – Scott Craner
    Sep 12, 2017 at 16:14
  • The quick and dirty way to fix this was to use cell formatting to make the numbers appear as I wanted them. Then I copied that column to a TXT file, saved it, opened it, and copied them back. Worked like a charm (although the destination column needs to be formatted as Text before you paste, or Excel will just decide they are dates all over again). Hat tip to Jake Miller.
    – boobounder
    Sep 12, 2017 at 23:54
  • @boobounder you may share the answer in the Answer tab, to remove this question from 'unanswered' list. ( :
    – p._phidot_
    Aug 9, 2018 at 10:31

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .