Scientific Notation Removes values when converting cell to Number

[JShearer]

Just recently I've run into this issue. Microsoft® Excel for Office 365 MSO (16.0.11328.20362) 32-bit

I process data files that have very long alpha/numeric strings and when bringing into Excel, only certain rows display as 2.1E+61.

When i change the cell to number format and no decimal, I lose the values. The issue appears to be in the number of contigous values in the cell.

I have a formula that extracts numbers from a certain location within the string, and the formula =IF(LEFT(B1,2)="21",MID(B1,26,4),"<>") should return 2238, but only returns 0000

The original data is this:

21000000030000000000000022380000000000004924000000000000000000

When I convert the cell to a Number format i get this and the data is lost:

21000000030000000000000000000000000000000000000000000000000000

I have another row that has the following data and it works correctly, i.e., no Scientific Notation and the left/mid formula works:

1202251900600000024620000001835000000111900016399

Any ideas on how to prevent Excel from altering the data?

 

[shg]

The data needs to be imported as Text and remain as text. Numbers in Excel (64-bit IEEE Doubles) only every display a max of 15 significant digits.

 

[JShearer]

Thanks that was the trick. I also created a blank workbook and marked all cells as text. I then use that as a template so I can copy/paste the data and keep my formulas intact.

 

[shg]

You're welcome.