Spreadsheets

Reference numbers turning into 1.23E+11 when opening a CSV

UPI and transaction reference numbers turn into 1.23E+11 in Excel. See when the digits are safe, when they are lost, and how to import them as text.

Editorial illustration for Reference numbers turning into 1.23E+11 when opening a CSV

Excel shows long reference numbers as 1.23E+11 because it reads them as numbers and the General format switches to scientific notation for long values. If the number has 15 digits or fewer, the digits are still stored and you can widen the column or format the cells as Number. If it has more than 15 digits, Excel has already replaced the extra digits with zeros, and only a fresh import as Text will get them back.

How can you tell whether anything has been lost?

Click a cell showing something like 1.23457E+11 and read the formula bar above the sheet. Count the digits. Suppose the original reference was 1234567890123456, which has 16 digits. The formula bar will show 1234567890123450, with a zero at the end that was never in the statement. Digits beyond the fifteenth cannot be recovered, because Excel keeps only 15 significant digits for a number.

Original referenceDigitsWhat Excel keeps after a normal open
12345678901212All digits are stored, shown as 1.23457E+11 until you widen or reformat the column
123456789012345616Digits after the fifteenth become zeros: 1234567890123450
00012345678912The leading zeros disappear, leaving 123456789

You can also compare against the source. Open the CSV in Notepad and look at the same row: if Notepad shows 1234567890123456 and Excel shows 1234567890123450, you know the sheet is wrong, not the bank.

Many UPI references are 12 digits, so they usually survive and only look odd, but check your own file, since card numbers and some other IDs are longer.

What is the quick fix when the digits are all there?

  1. Select the column and choose Home > Number Format > Number, then set decimal places to 0.
  2. Widen the column by double-clicking the border between its letter and the next.
  3. If you need to match references against other lists, convert them to text: in a new column, enter =TEXT(A2,"0") and fill down.

These fixes do not restore leading zeros, so they suit only references that never start with zero.

Why is fixing it after opening too late?

Format as Text changes how a value is treated from then on. It does not repair what was already converted. By the time Excel has opened the CSV, the number conversion has happened, and the rounding is saved the moment you press Ctrl+S. The safe moment to decide is before the data is loaded.

How do you import references as text?

  1. Open a blank workbook and choose Data > Get Data > From File > From Text/CSV.
  2. Pick the statement file and, in the preview, set Data Type Detection to Do not detect data types.
  3. Choose Transform Data, select the reference column, and make sure its type is Text.
  4. Choose Home > Close & Load.

Afterwards, put =LEN(A2) in a spare column and fill it down. If your bank uses references of one fixed length, every row should return that same number, and any row that does not deserves a look. Delete the helper column when you are done.

If you prefer the old wizard, go to File > Options > Data and tick From Text (Legacy), then use Data > Get Data > Legacy Wizards > From Text (Legacy). On step 3, click the reference column and choose Text.

Cheque numbers and account numbers have the same weakness, so give them the same Text setting. A cheque number such as 000123 loses its leading zeros when Excel treats it as a number, and that can break matching against your cheque book.

Checklist for next time

  • Import through From Text/CSV, never by double-click.
  • Set reference, cheque and account columns to Text.
  • Spot-check the last digits of three references against the bank's own statement view.
  • Save the finished sheet as an XLSX, so Excel does not re-convert them on the next open.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports