Excel shows date instead of numbers separated by slash '/'

Question

I have following contents in my CSV file

"Name","Door Number","Street Name"
Kumar,3/17,XYZ st
Ram,3,ABC st
Leela,2/12,XYZ st

When I open this .csv file in excel, for the Door Number column it shows 17-Mar instead of 3/17, shows 12-Feb instead of 2/12. How to get rid of this issue? Thanks in advance.

Answer 1

It's a default formatting thing in Excel, there's no way to stop it from the CSV.

Answer 2

Due to your Regional Setting, excel thinks its a date. Maybe you can turn it back to the original with:

=TEXT(B1, "m/d")

value in B1

Answer 3

Two options:

1. If you can change the csv source, you can use the answer to this question and change the fields to appear like "=""3/17".

2. If you can't change the csv, you could process the csv using csv2odf, like this:

   a. Create a template in Excel with a row of heading and a row of dummy data, place text in the column where the slash might appear so that Excel will read it as text. Save it as xlsx (xls will not work).

   b. Run this command:

   csv2odf yourdata.csv yourtemplate.xlsx output.xlsx