Keep an E-shaped product code as text

When a code looks like a power of ten

Part numbers, shelf locations and lot codes sometimes combine digits with the letter E: 123E5, 4E12, 70E3. Spreadsheets read that pattern as scientific notation, so 123E5 can turn into 123 times ten to the fifth, which is 12,300,000. The original code is gone from the cell, and saving the file makes the change permanent.

The text-safe converter writes every cell as an explicit text cell, so the spreadsheet receives the five characters 1, 2, 3, E and 5 and has no number to interpret.

The risk is not limited to E. Depending on the program and its settings, codes made of digits with a slash or a dash can be read as dates. The text-only export protects those in the same way, because no cell in the workbook is written as a number or a date.

The 123E5 example

The page example has one column named code with the value 123E5. In the workbook the expected cell text is 123E5, stored as an explicit text cell, not as a formula and not as a number. The converter does not decide whether the code names a part, a shelf or a measurement; it only keeps the characters.

Exporting a code column safely

• Paste the code column, with any neighbouring columns you need, into Table text and select its Delimiter.
• Press Calculate and check that the code column in the preview shows every E and every digit exactly as in the source.
• Confirm that the letter really is E and not the digit 8 or the letter F; photos and old printouts confuse them.
• Use Download (XLSX) and open tablora-table.xlsx rather than a CSV copy.
• Look at the cell in the spreadsheet's formula bar to confirm it holds the code as text.

How the other tools treat 123E5

• The numeric normalizer does not read exponent notation. A value like 123E5 in a selected Number column is held under Values left for review and keeps its text, so it never becomes 12300000 there.
• The decimal totals checker rejects it as a value, because only plain decimals with an optional minus are accepted.
• In the Tablora app, the XLSX export does not treat a value with a letter E as a number either, so it stays text in the workbook. The app's CSV export writes it as read, which leaves the interpretation to whatever program opens the CSV.
• If a column holds real measurements written in exponent form, convert them in your spreadsheet as a separate, reviewed step, and keep the original column.

Original synthetic example

Input

code 123E5

Expected cell text

123E5

Cell storage

Explicit text, not a formula or numeric cell

Tools

• text-safe-xlsx

Sources

• Microsoft: Keeping leading zeros and large numbers