0

Background:

  • I'm using Excel to organize notes and keep inventory counts for items in our warehouse. We have location codes to help us find items in the warehouse in the format [letter][number]-[number]. For example, A1-1 or C3-4
  • I'm using Office 365 on the "slow insider build" Version 15.33 (170403) (but have been experiencing this issue for several months).
  • On Mac OSX 10.12.5 Beta (16F43c)

Problem:

  • All of our location codes that begin with an A get converted to what appears to be a date in some foreign script in the sheet, and a date in the formula bar.
  • This can be prevented when I'm manually putting location codes into the spreadsheet by prefacing the location code with a backtick, however I'm not sure how to prevent the conversion when importing a csv from our ERP system (other than using Google sheets instead of Excel)
  • If I "format cells", it either turns into an (effectively, to me) random number or date, and I think I'm losing my original data
  • If I copy and paste the cell outside of Excel, it turns into yet another format of a date

Example:

A1-1 becomes 10/2/2016 in the formula bar, copies as 2-Oct and looks like this in the sheet

Questions:

  • How can I prevent this from happening on import?
  • How can I reverse the conversion and get my original data back?
1
  • The original input is as good as lost. You must import it again. Just as the answer below, when you import make sure you format the column as text to prevent this from happening again. – Vylix Apr 12 '17 at 11:18
1

If you import your CSV file using the text import wizard, you can select "text" as the data format for your "location date" column at step 3.

It looks like this:

Import - Text

For more information on "Text import wizard", see here.

3
  • I thought that the Text import wizard was the answer, but I couldn't figure out how to select columns that were further to the right. I guess I'll keep playing with it. – Jacobdh Apr 13 '17 at 17:21
  • You can scroll to the right using the scrollbar at the bottom of "data preview" frame in the image above. You can even select multiple columns using either Ctrl + click, or Shift + click – mr.mams Apr 13 '17 at 18:36
  • (can't upvote comments yet, so...) thank you! – Jacobdh Apr 14 '17 at 0:48

Your Answer

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

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