To stop Excel scientific notation on account, card and reference numbers, set those columns to Text before the data arrives, either in the import step or by formatting empty cells first. Excel then keeps every digit as typed. Reformatting afterwards only changes the display, and digits beyond the fifteenth may already be gone.
Why does Excel show a long reference number as 1.23E+15?
Excel shows 1.23E+15 because it read the reference as a number and the General format has no room for all its digits. Scientific notation is Excel's short way of writing a large value: 1.23E+15 means 1.23 times ten to the fifteenth. The cell still holds the number, just displayed compactly.
That display rule is sensible for physics and useless for a statement. A transaction reference, a card number or a long account number is an identifier. Nobody adds two of them together, and every digit has to stay readable. Excel cannot know that, so it treats any run of digits as a quantity.
Converted statements hit this often. Reference and check columns are pure digits, and the converter hands them to Excel with nothing to mark them as labels. The amounts and dates in the same file come through fine, which makes the reference column the odd one out.
Widening the column sometimes brings the digits back on screen, and setting a Number format with zero decimal places shows the whole value. Both work only while the number is short enough for Excel to hold exactly, which is the subject of the next section.
The 15-digit precision limit on card and account numbers
Excel keeps at most 15 significant digits of any number. A 16-digit card number typed or imported as a number loses its last digit, which Excel replaces with a zero. Microsoft's article on long numbers in Excel states the limit and the same fix this post uses.
The loss is silent. The cell shows 4111111111111110 where the statement printed 4111111111111111, and nothing warns you. Two different references that differ only in the final digit become the same number, so a lookup or a duplicate check matches rows that are not the same.
Here is what survives, by length of the identifier:
| Identifier length (example) | Stored as a number | Can you recover it in Excel? |
|---|---|---|
| 9 digits, such as a check reference | Every digit kept | Yes, by changing the format |
| 15 digits | Every digit kept | Yes, by changing the format to Number with no decimals |
| 16 digits, such as a card number | Last digit becomes 0 | No, re-import from the source |
| 20 digits, such as some transaction IDs | Last five digits become 0 | No, re-import from the source |
The rule of thumb is simple. Up to 15 digits, the problem is only how the cell looks. Past 15, the data itself has changed.
Setting reference columns to Text before the import
Setting the column to Text before the import is the dependable way to keep every digit of a long identifier. Excel stores Text cells exactly as they arrive, so nothing is rounded and nothing switches to scientific notation. The setting has to come first, because formatting a column after the import does not restore digits already lost.
For a CSV from a converter, use Power Query:
- Open a blank workbook and choose Data, then From Text/CSV.
- Pick the file and choose Transform Data rather than Load.
- Click the header of the reference column.
- On the Home tab, set Data Type to Text, and choose Replace current if asked.
- Repeat for any other identifier column, such as a card or account number.
- Choose Close & Load.
The same Microsoft article also describes an Automatic Data Conversions setting in recent Microsoft 365 versions that can switch off the 15-digit truncation, but a Text column works in every version. For values you paste or type by hand, select the empty cells, press Ctrl+1, and choose Text before pasting. A single entry can also start with an apostrophe, as in '4111111111111111. The apostrophe stays hidden in the cell.
Text columns come with one side effect. They sort character by character, which is right for references and wrong for dates and amounts. Keep the Text setting to identifier columns only, and sort the transactions by date as usual.
Can digits lost to scientific notation be recovered?
Digits are recoverable only if the number had 15 or fewer of them. For those, select the column and choose Number with no decimal places, and the full value shows again. For longer numbers, the extra digits are gone from the workbook, and the only fix is to bring the column in again as Text.
Choosing the Text format does not help here, because it does not change numbers already entered.
Undo is the exception. If you just pasted or opened the data, Ctrl+Z restores the original state, and you can format the column as Text and paste again.
Re-importing is less work than it sounds. You do not need the whole file again. Import the source a second time with only the reference column set to Text, then copy that column over the damaged one. Check that the row count matches before you paste, so each reference lands on its own transaction.
If the reference column was your way of keeping transactions in their printed order, a damaged column can scramble that too. Putting rows back in order after a PDF conversion offers other ways to restore the sequence. A statement split across several page sheets is easier to repair after you combine the pages onto one Excel sheet, so the fix runs once instead of once per page.
When scientific notation on an amount column is harmless
Scientific notation on an amount column is usually a display glitch, not data loss. A real money amount on a personal or small business statement never comes near 15 digits, so the value is intact. A narrow column or a stray format is the cause, and widening the column or applying Number fixes it.
Leave the Text setting off amount columns. A Text amount does not add up, and the fix for that is a separate job. The same goes for account numbers you never look up: if a column only identifies the account and every row holds the same value, you can delete it rather than repair it.
UK statements bring their own mix of short identifiers, including sort codes and eight-digit account numbers, and converting a UK bank statement to Excel covers how they behave. FastBankConvert is getting ready to open, and it is built to put amounts, dates and descriptions into separate columns; Get started adds you to the list for trial conversions.