Number Formatting

[kinman10]

Hey all, 

I have a lot of work books that are exported from a CRM system. When I’m exporting the most important thing that I need is the account number, which is often times a long up to 16 digit number that is never formatted correctly even when I set the column to “text” it almost always changes the last digit of the account number to 0 instead of keeping the actual last digit of the account number. I know the putting a ‘ in front of the number trick but sometimes I get the same result and that has to be done manually. Is there an automated way to go about keeping the account number intact? These exports can be hundreds of accounts so double checking them and fixing them manually is too time consuming. 

[#2]

@kinman10 Important to know in what format the CRM systems exports the reports. If it produces XLS(X) files the problem lies there. Excel can't display numbers with more than 15 digits as you have discovered.

If you have the option to export raw data in CSV format, that would be the way the go. But, then you must properly import that data, preferably with Power Query, into Excel, rather than just open the CSV in Excel by double-clicking on the file name.

So, could you please clarify what the CRM exactly produces?