How to prevent MS Excel on OSX from converting "a1-1" (or similar) to a date and/or convert it back?

Question

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:

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, Commented Apr 12, 2017 at 11:18

Vylix · Accepted Answer · 2017-04-12 09:31:55Z

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:

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

Vylix

1,7544 gold badges22 silver badges26 bronze badges

answered Apr 11, 2017 at 21:55

mr.mams

1481 silver badge7 bronze badges

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
Commented Apr 13, 2017 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
Commented Apr 13, 2017 at 18:36
(can't upvote comments yet, so...) thank you!
– Jacobdh
Commented Apr 14, 2017 at 0:48

Add a comment |

1 Answer 1