CSV Opens Wrong in Excel? Fix Columns, Encoding, Zeros and Dates

A CSV opens wrongly in Excel because double-clicking it makes Excel guess three things from your PC's settings: the separator, the text encoding and the type of every value. When a guess is wrong you get the classic symptoms: everything in column A, Hindi or ₹ turned into symbols like ₹, phone numbers that lose their first 0, Aadhaar numbers shown as 1.23E+11, and codes like 1-5 turned into dates.

The cure for all of them is the same: don't double-click. Open the file with Data › From Text/CSV and tell Excel the encoding, the delimiter and which columns are text. If only the columns are the problem, the free CSV to Excel converter detects the separator for you. Below, each problem is explained with what we saw when testing in Excel 2021 on a Windows PC set to English (India).

Quick diagnosis

Problem 1: everything lands in one column

CSV means comma-separated values, but Excel doesn't simply split on commas. When you double-click a file, it splits on the list separator set in your Windows region settings. Indian and US settings use a comma. Many European settings use a comma as the decimal point (1500,50), so their list separator is a semicolon, and software from those countries exports semicolon-separated "CSV" files.

Open a semicolon file on an Indian PC and Excel finds no commas, so each whole line stays in column A. In our test the line Asha;Pune;1500,50 did worse: Excel split it at the decimal comma, putting Asha;Pune;1500 in column A and 50 in column B.

Fixes, from best to quickest:

• Import it. Data › From Text/CSV, choose the file, set Delimiter to Semicolon, then Load.
• Split it after opening. Select column A, then Data › Text to Columns › Delimited, tick Semicolon and click Finish.
• Add a hint line. Put sep=; on its own first line in Notepad. Excel 2021 then split our file correctly on a double-click. It isn't part of any CSV standard, so remove it before giving the file to other software, which will read it as a data row.
• Convert it. The CSV to Excel converter detects commas, semicolons and tabs by itself. It reads the decimal comma wrongly though: 1500,50 became 150050. For European numbers, use Power Query: right-click the column, choose Change Type › Using Locale, then Decimal Number and the file's country.

You can also change the list separator in Windows (Control Panel › Region › Additional settings). Microsoft warns that this changes every program on the PC, so it is rarely worth it for one file.

Problem 2: Hindi, Tamil or ₹ turn into symbols

A CSV is plain text, and plain text needs an encoding to turn bytes into letters. Modern software writes UTF-8, where the ₹ sign is three bytes (E2 82 B9) and each Devanagari letter is three bytes too. On a double-click, Excel on Windows reads a CSV in the PC's old "ANSI" code page (Windows-1252 on English Windows) unless the file begins with a byte-order mark (BOM), the three bytes EF BB BF that announce UTF-8. Read as Windows-1252, ₹ shows as ₹ and नाम shows as नाम. Newer Microsoft 365 builds may guess UTF-8 more often, but don't count on it.

In our test, the same file opened perfectly once it had a BOM. Excel even recognised ₹1500 as a currency amount. To fix a file:

• Import with the right origin. In Data › From Text/CSV, set File Origin to 65001: Unicode (UTF-8). Microsoft's Power Query documentation notes that UTF-8 is detected automatically only when the file starts with a BOM.
• Add the BOM. Open the CSV in Notepad, choose File › Save as, set Encoding to UTF-8 with BOM, and save. After that, double-clicking works.
• Export correctly in the first place. When saving from Excel, choose CSV UTF-8 (Comma delimited), which writes the BOM.

If you see question marks (????) rather than symbols, the damage happened earlier. The older CSV (Comma delimited) save option writes ANSI, which has no Devanagari letters, so in our test the name अमित came out as ???? and the ₹ sign as ?. The original characters are gone from that file; export again from the workbook as CSV UTF-8.

Problem 3: leading zeros disappear

Excel reads 011, 0124 and 09876543210 as numbers, and a number has no leading zeros, so they become 11, 124 and 9876543210. Indian PIN codes never start with 0 and are safe, but STD codes, employee codes, roll numbers and ZIP codes are not.

• In Data › From Text/CSV, set Data Type Detection to Do not detect data types, so every column loads as text. Or click Transform Data and change just the code columns to Text. If Power Query asks whether to replace the current type step or add a new one, choose Replace current; a new step would only turn the already-shortened 11 into the text "11".
• In Excel for Microsoft 365 and Excel 2024, go to File › Options › Data and, under Automatic Data Conversion, untick Remove leading zeros and convert to number. Excel 2021 and older don't have this setting.
• Never press Save on the CSV after a double-click. Excel writes back what it shows, so the zeros are lost from the file as well.

Problem 4: long numbers show as 1.23E+11 or end in 0

Two separate rules are at work. First, the General format shows any number of 12 or more digits in scientific notation, so a 12-digit Aadhaar number like 123456789012 appears as 1.23457E+11 (or 1.23E+11 in a narrow column). The value is still complete. Select the column, press Ctrl+1, choose Number and set decimal places to 0, and every digit shows.

Second, Excel keeps only 15 significant digits. In our test the 16-digit card number 4111111111111111 became 4111111111111110, and Microsoft confirms that digits after the 15th are changed to zero. No format brings them back, so 16- to 19-digit card, reference and account numbers must be imported as Text. In Excel for Microsoft 365 and Excel 2024 you can also untick Keep the first 15 digits of long numbers and display in scientific notation if required, which stores such numbers as text.

A 10-digit mobile number keeps its digits but may show as 9.88E+09 until you widen the column.

Problem 5: dates swap, or codes become dates

Excel reads dates in your Windows date order. On English (India) settings that is day-month-year, so 03/04/2026 opened as 3 April 2026 and 15/08/2026 as 15 August. A PC set to US English reads the same text as 4 March and leaves 15/08/2026 as text, because there is no 15th month. A file can therefore look right on your PC and wrong on a colleague's.

Excel also turns date-like codes into dates: our product code 1-5 became 01-May (1 May 2026). Fixes:

• Import code columns as Text, as in Problem 3.
• For a real date column from another country, use Power Query's Change Type › Using Locale and pick Date with that country's locale, for example English (United States) for MM/DD files.
• When you create files, write dates as 2026-04-03. Year-month-day is never read the wrong way round.

The safe way to open any CSV in Excel

1. Open a blank workbook and choose Data › From Text/CSV.
2. Set File Origin to 65001: Unicode (UTF-8) unless you know the file is ANSI.
3. Check the Delimiter and the preview columns.
4. Set Data Type Detection to Do not detect data types, or click Transform Data and set each column's type yourself.
5. Click Load, then save as .xlsx so you never re-open the raw CSV by accident.

Converting the file first with our converter solves Problem 1 only. It still drops leading zeros, reads slash dates month-first and needs a BOM for Hindi; the guide how to convert CSV to Excel shows its results row by row. To understand why CSV behaves like this at all, read Excel vs CSV.

Frequently asked questions

Why does the same CSV open fine on my colleague's computer?

Your PCs probably have different region settings (list separator and date order) or different Excel versions. A semicolon file opens correctly where the list separator is a semicolon, and 03/04/2026 means different dates on Indian and US settings. Importing with explicit settings gives the same result everywhere.

How do I stop Excel converting numbers and dates for good?

In Excel for Microsoft 365 or Excel 2024, untick the options under File › Options › Data › Automatic Data Conversion. They cover leading zeros, long numbers, numbers with an E, and letter-number dates like JAN1, but values such as 1-5 can still become dates. Importing those columns as Text is the only complete fix.

Can I get my leading zeros back after saving?

Only if every code had the same length. For six-digit codes, =TEXT(A2,"000000") in a new column rebuilds them as text. If the lengths varied, the information is gone; go back to the original export.

Does Google Sheets have the same problems?

Partly. File › Import detects the separator automatically, but it also converts numbers and dates unless you untick Convert text to numbers, dates, and formulas, which then keeps every column as text.

Which save option should I use so the CSV opens correctly next time?

Save as CSV UTF-8 (Comma delimited) to keep Hindi and ₹, and keep the .xlsx as your working copy. The Excel to CSV guide compares the save options, and the Excel to CSV converter works without Excel.

Sources: Microsoft Support: Import or export text (.txt or .csv) files; Keep leading zeros and large numbers; Available number formats in Excel; Set automatic data conversions; Excel specifications and limits. Microsoft Learn: Power Query Text/CSV connector; Data types in Power Query. Tests in Excel 2021 on Windows with English (India) settings, October 2026.