To import a CSV into Excel as text, use Data > From Text/CSV, choose the file, select Transform Data, set the relevant columns to Text in Power Query, then choose Home > Close & Load. This prevents Excel from interpreting identifiers as numbers or dates during import, preserving values such as 00127 and long account codes.
Why Excel changes CSV values
A CSV file stores rows and columns of text separated by a delimiter, usually a comma. It does not reliably record spreadsheet details such as a cell’s number format or whether a value is meant to be an ID, a date, or a number. When Excel opens a CSV directly, it tries to infer what each field represents. That can be helpful for ordinary data, but it can also change values that should remain exactly as written.
For example, Excel may display 00127 as 127, interpret 3/4 as a date, or display a long number in scientific notation. Excel also only preserves 15 significant digits in numeric values. If an identifier contains more digits, converting it to a number can change digits beyond that precision. These are not just display changes: the underlying cell value may be different from the original text.
Importing a CSV as text means telling Excel to treat chosen fields as text at the point of import. Text cells retain the characters in the CSV rather than being converted into numeric or date values. This is particularly useful for product codes, postal codes, phone numbers, invoice numbers, tracking IDs, and values with leading zeros.
Recommended method: import with Power Query
In Excel for Microsoft 365 on Windows, the most flexible method is to import the file through Power Query and assign the text type before loading it into the worksheet. The menu names below refer to the Windows desktop app.
- Open a blank workbook or the workbook where the data should go. On the ribbon, select Data > From Text/CSV, then browse to and select the CSV file.
- Check the preview. Confirm that Excel has identified the correct delimiter, such as comma, semicolon, or tab, and that the fields appear in the expected columns. If the preview is wrong, choose the appropriate delimiter in the import dialog.
- Select Transform Data rather than loading immediately. This opens the Power Query Editor, where you can set column types before the data reaches the worksheet.
- Select the column or columns that must stay unchanged as text. To select adjacent columns, click the first header and Shift-click the last; for nonadjacent columns, use Ctrl-click.
- Choose Transform > Data Type > Text. If Excel asks whether to replace the current step or add a new one, choose the option that applies the text type at the appropriate point before any conversion that could alter the value.
- Check the preview for leading zeros, date-like values, and long identifiers. Then select Home > Close & Load to put the imported data into Excel.
You do not have to make every column text. For example, you might set a customer ID column to Text while leaving an Amount column as a number, so it remains available for sums and calculations. The key is to identify special fields before loading, rather than trying to restore them after Excel has converted them.
Choosing which columns should be text
Use text for a field when its characters are meaningful as written and arithmetic is not the purpose. A postal code such as 02139 is an identifier, not a quantity to add. Likewise, a product code such as 000584 should normally retain its zeros even if every character is a digit. A phone number may contain a plus sign, spaces, or parentheses, which are also part of how the number is represented rather than something to calculate with.
Typical candidates for text include:
- Account, customer, employee, and order IDs.
- Postal or ZIP codes, including codes that begin with zero.
- Phone numbers, barcodes, tracking numbers, and product SKUs.
- Long digit strings that exceed Excel’s 15-digit numeric precision.
- Dates or number-like entries that must remain literal codes, such as
1-2in a classification field.
By contrast, leave quantities, prices, and genuine dates as numeric or date values when you expect to sort them chronologically, calculate with them, or use them in date functions. A cell containing the text 2026-05-18 may look like a date but does not behave as a date in formulas until converted. The right type depends on what the field means, not just what its characters look like.
Setting all columns to text
If the CSV is a collection of codes, unprocessed records, or fields whose meanings are not yet known, you may prefer to set all columns to Text. In Power Query, select the columns in the preview and choose Transform > Data Type > Text. Review the result before loading, because this also means values such as amounts and dates will arrive as text and will not work as ordinary numbers or dates until you deliberately convert them.
For mixed data, apply Text only to the sensitive columns. Keeping a numeric sales column numeric, for example, makes it possible to total it with a PivotTable or a formula without an extra conversion step. A useful import practice is to document which columns are identifiers and which are measures, especially when the same CSV structure will be imported repeatedly.
Using the legacy Text Import Wizard
Some users prefer the older Text Import Wizard because it lets them mark individual fields as Text in a familiar column-by-column preview. In Excel for Microsoft 365 on Windows, the legacy wizard may need to be enabled first: go to File > Options > Data, find Show legacy data import wizards, and enable From Text (Legacy). Then use Data > Get Data > Legacy Wizards > From Text (Legacy) to choose the CSV.
In the wizard, select the delimiter and confirm the column boundaries. On the final step, select a column in the preview and choose Text as its column data format; repeat for each column that must be preserved. Finish the wizard to import. The exact availability and labels can vary with Excel version and configuration. Power Query is generally the better choice for repeatable imports because it records the transformation steps and can refresh them later.
Common problems and how to fix them
The zeros have already disappeared. Changing a converted number’s cell format to Text does not restore missing zeros. For example, formatting a cell containing 127 as Text will not recreate the original 00127. Import the source CSV again and set the column to Text before conversion. If the source file itself no longer contains the zeros, Excel cannot infer how many were removed.
A long identifier has changed at the end. If Excel converted more than 15 digits to a number, some digits may already have been lost. Changing the data type after that conversion cannot reliably recover them. Re-import from the original CSV and assign Text before the numeric conversion occurs.
Dates appear in a different format. First decide whether the field represents a real date or a literal code. If it is a real date, an alternative display format may be all you need; if the exact characters must be retained, import it as Text. Regional settings can also affect how date-like strings are interpreted, so a value such as 04/05/2026 may be ambiguous between month-first and day-first conventions.
Everything appears in one column. Check the delimiter in the import preview. A file created in a region that uses commas as decimal separators may use semicolons between fields. Also check that the file is actually a delimited text file, not a differently structured export that only has a CSV-like filename.
Text seems to have shifted into the wrong columns. CSV files can contain delimiters inside a field when that field is enclosed in quotation marks. A proper CSV parser recognizes this convention. If a file was generated incorrectly or uses an unusual delimiter or encoding, the preview may not split it as expected; inspect the source file and adjust the import settings rather than editing thousands of misaligned cells by hand.
Encoding, delimiters, and quotation marks
Data type is only one part of a reliable import. The delimiter determines where one field ends and the next begins. The file’s encoding determines how characters such as accented letters and non-Latin scripts are read. If names display as garbled characters, select the appropriate file origin or encoding option in the import dialog and check the preview before loading. UTF-8 is common, but not every CSV is encoded the same way.
Quotation marks in CSV commonly enclose a field that contains a delimiter or a line break. For example, a comma inside a quoted address should remain part of that address rather than create another column. Excel’s import tools parse CSV quotation conventions; the enclosing quotes are generally syntax, not characters that should appear in the resulting cell. If a quotation mark is intended as part of the actual value, it may be escaped in the CSV source.
Refreshing the import and saving the result
Power Query can be useful when the same report arrives every week or month. After loading the query, replace the source CSV with an updated file at the same location and use Data > Refresh All to rerun the recorded steps. The text type assignment is part of those steps, so it can be applied again on refresh. If the new file has different column names or a changed layout, check the query and its output rather than assuming the refresh produced the intended table.
Save the finished workbook as an Excel workbook, typically .xlsx, if you want to retain the query, worksheet formatting, and Excel data types. CSV is a plain-text exchange format and does not retain workbook features such as multiple sheets, formulas as formulas, or cell formatting. If you save a worksheet back to CSV, Excel may warn that some workbook features will be lost. Also keep the original CSV if you need an untouched source record for comparison or future re-imports.
Other ways to keep imported values as text
For a small amount of data, you can format destination cells as Text before pasting or entering values, but this is not as dependable as setting types during a structured import. If Excel has already interpreted a CSV field, changing the cell format afterward may change only its appearance, not restore the original characters.
Another option is to prefix a manually entered value with an apostrophe, such as '00127. Excel treats the entry as text and does not display the leading apostrophe in the cell. This can help for a one-off entry, but it is not a good bulk-import strategy: inserting characters into a source column can also affect later exports or systems that expect the original value. For large or recurring files, use Power Query or the Text Import Wizard.
Excel for Mac and Excel for the web do not always have the same import tools or menu labels as Excel for Microsoft 365 on Windows. Power Query features and CSV options can depend on the version and platform. If a particular workflow is unavailable, use the import controls offered by that version to identify the relevant columns as text before loading; avoid opening the CSV by double-clicking when exact preservation matters.
Frequently Asked Questions
How do I stop Excel from removing leading zeros in a CSV?
Import through Data > From Text/CSV, choose Transform Data, set the affected column to Text in Power Query, and then load it. If the zeros have already been removed, re-import from a source file that still contains them.
Can I make every column in a CSV import as text?
Yes. In Power Query, select all the columns and choose Transform > Data Type > Text before loading. Remember that dates and amounts will also be text, so convert those fields deliberately if you need date sorting or calculations.
Can I change a number column to Text after importing?
You can change its type, but that will not restore information Excel already discarded, such as leading zeros or digits beyond its numeric precision. Re-importing and setting the type to Text before conversion is the safer fix.
Why does opening a CSV directly give different results?
When Excel opens a CSV directly, it may automatically interpret fields as numbers, dates, or other values. Importing through the Data tab gives you a chance to check the delimiter and set sensitive columns to Text before the data is loaded.
Will the text setting remain when I refresh the CSV?
When the import is set up as a Power Query, its type-change step is normally applied again during refresh. Check the query if the file’s columns or structure change, since a changed source can affect the result.
Does a CSV store a column as text?
A CSV stores delimited text, not Excel’s full cell-type and formatting information. Excel decides how to interpret many fields when it imports or opens the file, which is why you must specify Text for identifiers that need to stay exactly as written.
Related
- Excel Goal Seek Tolerance: Set Precision and Check Results
- Excel Goal Seek Integer? How to Get Whole-Number Results
- Excel Goal Seek Maximum Value: Find the Actual Maximum
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Subtotal Visible Rows: Sum Filtered Data CorrectlyTo subtotal only the rows visible after filtering in Excel, use the SUBTOTAL function with function number 9…
- Excel Shortcut Codes: Essential Keys and How They WorkExcel shortcut codes usually means keyboard shortcuts: press a key or combination of keys to run a command…
- Find Variable Names in Excel: A Practical GuideTo find variable names in Excel, open Formulas > Name Manager to review workbook-defined names, or use the…
- Excel Heatmap Formatting: Color Scales and TipsTo format a heatmap in Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home…
- How to Add and Format Numbered Footnotes in WordTo add a numbered footnote in Microsoft Word, click where its reference number should appear, then choose References…
- Does Block Sender in Outlook Really Work? What to KnowYes—Outlook’s Block Sender feature works by directing future email from a blocked address to Junk Email, rather than…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.