How to Prevent Excel Text to Columns from Converting Numbers and Dates
Question details
The user wants to stop Excel from automatically converting long numbers into scientific notation and date-like text into dates when importing delimited files or using the Text to Columns feature.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing delimited data files or splitting text into columns.
- Observed behavior
- Excel automatically converts long serial numbers to scientific notation and date-like strings to actual date formats, ignoring prior cell formatting when files are opened directly.
Ensure you have your original delimited file (like CSV or TXT) available, and do not open it by directly double-clicking, as this triggers Excel's automatic formatting before you can intervene.
Set Column Format to Text in the Text to Columns Wizard
By explicitly setting the column format to 'Text' during the import or split process, you force Excel to preserve the original formatting of your numbers and dates.
When Excel processes delimited data, it applies a 'General' format by default, which guesses the data type. Overriding this default behavior in Step 3 of the wizard is the most reliable way to prevent unwanted conversions.
Instead of opening the file directly, open a blank Excel workbook. Go to the Data tab and choose 'From Text/CSV', or paste your data and click 'Text to Columns'.
Proceed through Step 1 and Step 2 of the Text to Columns Wizard by defining your delimiters (e.g., comma, tab) and click 'Next'.
In Step 3, look at the 'Data preview' section at the bottom of the dialog box and click on the header of the column containing your long numbers or date-like text to highlight it.
Under the 'Column data format' options in the upper-right area, select the 'Text' radio button instead of 'General'.
Click 'Finish'. Excel will now import or split the data exactly as it appears in the source, without applying scientific notation or date formatting.
Use Power Query to Explicitly Define Data Types
Power Query provides robust control over data types during the import process, preventing unwanted conversions before the data even reaches your spreadsheet.
Prevent Unwanted Data Conversion with WPS Office
WPS Spreadsheet offers a highly intuitive Text to Columns feature that allows you to lock your data formats as text effortlessly. It handles CSV and TXT files perfectly, ensuring long serial numbers and date-like values remain exactly as you intend.
- 1. Open data in WPS Spreadsheet: Launch WPS Spreadsheet and select the cells containing the data you wish to split or parse.
- 2. Access Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' tool.
- 3. Set column format to text: Follow the wizard to Step 3, highlight the specific columns in the preview pane, and choose 'Text' under the Column Data Format settings.
- 4. Apply and finish: Click 'Finish' to complete the data split while keeping all your long numbers and dates perfectly intact.
Frequently Asked Questions
Why does Excel convert my long numbers into scientific notation?
Excel has a maximum precision limit of 15 digits for numeric values. When you import or paste a number longer than 15 digits (such as credit card numbers or tracking IDs), Excel automatically converts the value to scientific notation or changes any digits after the 15th to zeros. Formatting the column as 'Text' prevents this behavior.
Can I format the cells as text before opening the file to stop this?
If you open a CSV or TXT file directly by double-clicking it in your file explorer, pre-formatting cells will not work. Excel automatically generates a new sheet and applies the default 'General' format before you have a chance to change it. You must use the Data Import wizard to enforce the text format.
How do I recover long numbers that were already converted and saved?
If a file was opened directly in Excel, the long numbers were truncated, and you then clicked 'Save', the original trailing digits are permanently lost. You cannot recover them from the saved file; you will need to obtain the original source file and re-import it using the correct Text to Columns settings.
