Large Numbers Display vs Storage

[ds_robb]

I wonder if someone could help as this is driving me bonkers! I have a very large excel file, close on a million lines which has in one of the columns partner EDI addresses which are a mix of alphanumeric characters. When it's totally numeric, the number could be 21+ digits long and the CSV file they are in has the field as GENERAL and the number surrounded by a leading =" and followed by a trailing ". If I manually remove the =" and trailing " the field changes to TEXT and the whole number can be seen. e.g. 1234567891011121314, however if I use find/replace to remove the =" and trailing " the number is immediately displayed in scientific notation 1.2345E+12 (for example) and the trailing part of the number becomes 0's and therefore doesn't now accurately represent the partner address. Now I know there is a difference between how Excel stores the number versus how it displays the number, but how can I get Excel by way of cell type or formula to always display the full length number in TEXT format.

 

[Alex Blakenburg]

Before you use the find and replace, format the whole column as TEXT.
If you are receiving the file as csv then consider opening it using Power Query and then you can clean up these sort of issues before you get it into excel and additionaly make it a refreshable process.

1 Million lines is too much information to make any sense of without further filtering and summarisation and is approaching the limits of an Excel Worksheet, so it might be a good time to start thinking about using Power Query and possibly the Data Model.
If you have any numbers that don't have the additional characters forcing it to be text and are longer that 15 digits you will most likely have already lost information by the time you get to this point.

Also what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)