What is the fastest way to fix garbled Korean characters?

Instead of opening the file directly in Excel, import it through Data > From Text/CSV. In the preview, change File Origin to UTF-8 or Korean (949), which resolves most cases.

Why are all values in one column?

You need to configure the delimiter and text qualifier. In the preview or wizard, specify the comma (or semicolon/tab) and the text qualifier (double quotation marks).

Can I prevent zeros from disappearing or values from changing into dates?

During import, set data type detection to 'Do Not Detect' and set the affected column type to 'Text.'

Why does a 15-digit number display as 1.23E+15?

Import it as text. If it has already been altered, import it again or re-enter it using the Text format.

What should I do with a CSV file containing more than 1 million rows?

Use Power Query to load it into the Data Model and analyze it with a PivotTable, or split the file and load it into multiple worksheets.

CSV corruption fix thumbnail: a large ‘CSV FIX’ title on the left and a Studio Ghibli-style scene with a Pomeranian named Kkongi and UTF-8/delimiter cards on the right

CONTENTS

How to Fix CSV Files That Look Garbled in Excel: UTF-8, Delimiters, and Auto-Conversion

Garbled CSV files in Excel are usually caused by encoding (UTF-8/CP949), delimiters (commas/semicolons/tabs), or auto-conversion (lost zeros or misidentified dates). Importing the file with the steps below can resolve character encoding, column splitting, and lost-zero issues at once.

Quick Fix (3 Minutes)

Select the file through Data > From Text/CSV (do not open it by double-clicking).
In the preview, set File Origin to UTF-8 (or 949), specify the Delimiter, and set Data Type Detection to Do Not Detect.
In the editor, set sensitive columns to Text, then select Close & Load.

Why Does It Look Garbled?

Encoding mismatch (UTF-8/BOM, CP949)
Different delimiters (commas/semicolons/tabs/pipes)
Columns shift because the text qualifier is ignored
Auto-conversion causes lost zeros, misidentified dates, or scientific notation

Recommended Import Methods

Power Query (Data > From Text/CSV)

Specify the file origin and delimiter, then set data type detection to Do Not Detect.
Manually set column types (ZIP codes/IDs = Text).

Text Import Wizard (Legacy)

Set File Origin to 65001 (UTF-8) or 949 (Korean).
Specify the delimiter and text qualifier (").
Set each column’s data format, such as Text or Date.

Quick Repair

If everything is in one column, use Data > Text to Columns or:

=TEXTSPLIT(A2, ",", , TRUE)

(If there are many commas within quotation marks, Power Query is recommended.)

Prevent Auto-Conversion

ZIP codes/card numbers: import as Text.
Dates: lock the format by locale; ISO (YYYY/MM/DD) is recommended.
16 digits or more: keep them as text and adjust only the display.

Line Breaks, Quotation Marks, and Semicolon CSV Files

Line breaks inside quotation marks are data, so use Power Query or the wizard.
Escape quotation marks with: "".
For semicolon- or pipe-delimited CSV files, simply change the delimiter.

Large CSV Files

If the file exceeds Excel’s limit of 1,048,576 rows, load it into the Data Model and then create a PivotTable.
Alternatively, split the source file and load it into multiple worksheets.

Troubleshooting

Issue	Cause	Solution
Garbled Korean characters	Encoding mismatch	Switch File Origin between UTF-8 and 949
Everything appears in one column	Delimiter/text qualifier ignored	Specify the delimiter and text qualifier
Shifted columns	Quotation marks not handled correctly	Set the text qualifier to `"`
Lost zeros/misidentified dates	Auto-conversion	Set data type detection to Do Not Detect and specify Text
Scientific notation for 16-digit numbers	Automatic number formatting	Import as Text
Row limit exceeded	1,048,576-row limit	Load into the Data Model or split the file

엑셀 VLOOKUP 다중조건 — 보조열/CHOOSE/INDEX·MATCH/XLOOKUP 비교 완전정복

How to Fix CSV Files That Look Garbled in Excel: UTF-8/CP949, Delimiters, Auto-Conversion, and Large Files