Large CSV numbers and scientific notation

Keep long tracking IDs intact and recognize exponent text that cannot be expanded.

Scientific notation is a display and a precision cut

A product code with leading zeros, such as 00127, is the subject of the leading-zero guide. This page is the next numeric trap: digits with no leading zero that Excel still refuses to keep literally. Microsoft’s note on large numbers describes the mechanism in one place. Excel drops leading zeros, and it also keeps only fifteen significant digits, then often shows the result in scientific notation such as 1.23E+15, so that formulas can run. A tracking identifier is not a quantity. Showing it as an exponent does not make it easier to audit. It makes the tail digits unknowable.

There is a second, different string that already contains the letter E. Microsoft documents a conversion that turns digits around that letter into a scientific number, with an example of the shape 123E5 becoming 1.23E+07. A measurement that was exported as exponent text can be a real magnitude. An account number that merely looks similar is not. The file does not label which one you have. The column name and the source system do.

Current Excel options can keep long digit strings as text and can leave an E-form as text. Microsoft says those controls exist in Excel for Microsoft 365 and Excel 2024, and that a CSV open can warn about automatic conversions. The warning is not a conversion you can undo after you have saved the workbook. If the status bar shows a scientific form and the formula bar does not show every original digit, the digits are already gone.

tracking_id,reading,label
12345678901234567,6.022E23,seventeen digits and an exponent
100000000000000,1.5e-10,fifteen digits and a tiny exponent
9999999999999999,1.23456789012345E+16,sixteen digits beside an already shortened value

The identifiers are synthetic. They are not card numbers or government identifiers. The readings are shaped like exponent text, including a lowercase e and a plus sign, so you can see that the tool treats them as text rather than as calculated magnitudes.

Which cells this version flags

Preview the sample in CSV to Excel. There are three data records and three columns. The long-number warning uses a narrow rule: a field that is only digits and is at least sixteen digits long. The seventeen-digit id and the sixteen-digit id match. The fifteen-digit id, 100000000000000, does not match that warning. Exponent text does not match it either, because of the dot and the letter.

Fifteen digits is Excel’s published precision limit, not a promise that a fifteen-digit integer will display in full. General format in Excel can still show a fifteen-digit value in scientific notation when the column is narrow, while the stored number keeps fifteen digits of precision. Column width and precision are different. This tool does not set a custom number format that forces every digit onto the screen, and it does not claim Excel will display the fifteen-digit sample as seventeen characters of text.

The last reading, 1.23456789012345E+16, is already an exponent string. Expanding it to a seventeen-digit integer would invent digits the file does not contain. Even the digits you can see are not a safe integer conversion inside a binary floating-point spreadsheet. Leave the characters as they are unless the source system gives you the full integer again.

Steps that keep the identifier

1. Start from the CSV, not from a workbook that already shows E+ in the identifier column.
2. Load scientific-notation.csv in the converter. Choose Comma and keep the header. Confirm the seventeen-digit value matches the file from the first digit through the seventh, not only the first few digits on screen. Widen the preview if you are skimming.
3. Leave tracking_id and reading on Text. Download the xlsx. Compare all three identifiers and all three readings with the CSV. The readings should still contain the letter E or e and must not be rewritten as a longer integer.
4. As a negative test, set tracking_id to Number. The seventeen-digit and sixteen-digit values block the download with a message about more than fifteen significant digits. The fifteen-digit value alone would be accepted as a number, which is why that column still needs Text if the identifier must stay literal. Reset the column to Text.
5. Set reading to Number instead. Every reading is rejected as not a plain decimal. The tool does not evaluate 6.022E23 or 1.5e-10. Reset to Text before you download.

Preserve the digits as text →

Plain decimals that you do want as numbers, such as a quantity of 12 in some other file, can be marked Number on their own column. Headers stay text. Empty numeric fields stay blank. A two-decimal display format does not apply to these identifiers and would be the wrong tool for an exponent. More than fifteen significant digits, leading zeros, commas and exponent letters all block the numeric path. The block names the source record, which includes the header in the count.

Common errors compared

Edge cases

A leading plus, a trailing exponent on a negative quantity, or a space inside 6.022 E23 is a different string. The sample’s tiny reading is 1.5e-10 with a lowercase letter and no spaces. The minus sits after the letter, so the field is not treated as formula-like. A value that begins with =, +, - or @ is a separate warning. CSV formula protection can prefix an apostrophe on export and intentionally changes that value. These three readings do not take that path. Do not turn protection on “just in case” and then expect the reading to match the source character for character if you later add a leading minus.

Significant digits in the numeric checker count digits from the first non-zero, including trailing zeros in the written form, and cap the count at fifteen. The checker does not promise that an accepted number will look the same in Excel’s general format, and it does not promise decimal arithmetic beyond what Excel’s numeric type does. A value written with more than fifteen significant digits is refused rather than rounded.

Integers that use a thousands separator are not scientific notation, but they break for a similar reason: the comma is not part of the plain-decimal grammar. See the delimiter and locale guide before you strip commas. Stripping them can turn a European decimal into an integer.

An identifier with letters, such as AB12345678901234567, will not match the digits-only warning and will not be accepted as a number. Text is already the right type. The danger is a later process that keeps only the digit run and then feeds it to Excel.

Duplicate detection is string comparison. 6.022E23 and 6.022e23 are different keys. 100000000000000 and 1.0E+14 are different keys. CSV clean will not treat them as the same magnitude. That is conservative and correct for a cleaner that does not know the column’s meaning.

What the workbook does not calculate

Text cells store the characters. They do not store an IEEE floating-point value, and they will not feed a chart until you convert a column that really is a measurement. If you need 6.022E23 as a magnitude, convert it in a system that documents its rounding, in a column separate from the original text. This website does not do that conversion.

The fifteen-digit id can be stored as a number if you insist, because it passes the checker. Doing so opts into Excel’s numeric display and into any later general-format surprise. For a tracking id, decline that option. The sample mixes that fifteen-digit id with longer ids in one column, so the column as a whole is unsafe to mark Number. One bad cell blocks the download. That is preferable to a file where two ids survived and one was rounded.

CSV export from the same tool writes the characters you previewed. It does not attach an Excel type. Opening that CSV in Excel can apply scientific notation again. When the receiver is Excel and the digits must remain inspectable, deliver the text-typed xlsx, not another CSV. Renaming a csv file to xlsx does not create a workbook.

Before and after: intact tokens versus a shortened exponent

Load /samples/scientific-notation.csv in CSV to Excel with Comma and a header. Leave tracking_id and reading on Text. The preview has 3 data records and 3 columns. A text workbook keeps these characters; it does not evaluate the exponents:

excel-roundtrip-symptoms.csv is a different teaching file. It already stores 1.23457E+16 as literal text. That string is not a reversible view of 12345678901234567, and loading it here will not grow the missing digits. Keep the intact sample; do not Save As over it from a general-format grid. See Excel Save As CSV pitfalls.

Step-by-step troubleshooting

1. The CSV already shows E+ or e- in an identifier. The digits are gone. Re-export from the source and keep the column on Text. Do not delete the letter E to “make a number.”
2. Number export is blocked on tracking_id. That is the 16-digit and 17-digit guard. Reset the column to Text. A fifteen-digit neighbor in the same column is not a reason to mark the whole column Number.
3. Number export is blocked on the reading column. Exponent text is not a plain decimal here. Leave it as text unless a different system, outside this tool, documents the conversion.
4. The long-number warning skipped 100000000000000. Expected. The warning starts at 16 digits. Identifiers of 15 digits still belong on Text if they must stay literal.
5. You need a quantity column in the same file to be numeric. Mark only that column. Headers stay text. Empty numeric fields stay blank. This sample has no quantity column.

Open CSV to Excel with the sample →

Honest limits

The tool will not reconstruct digits from a value that is already stored as 1.23457E+16 or as the sample’s 1.23456789012345E+16. Those strings are the whole fact. If your source system still has the integer, export again as text. Do not pad with zeros to make a length match.

Excel’s fifteen-digit rule is Excel’s. Other databases use exact decimal or integer types with different limits. Passing the TableMender numeric check does not mean a warehouse will accept the value, and failing it does not mean the text is corrupt. It means “do not put this token in an Excel number cell.”

The long-number highlight is a warning list, not a full audit log. It does not rewrite cells. Pair it with a direct comparison of a few full identifiers, including one near the bottom of a real file, not only the first page of the preview. Preview pagination does not change the export. The export scope does.

← All guides