Copy/paste from Dynamics NAV to Excel Headache!

Every time you copy/paste data from NAV to Excel, you always run into the “intelligent” way Excel handles data. Excel’s default number format is “General”. Unfortunately this has some drawbacks, especially when you paste in data from another application.

Lets do this example, enter in a new cell with Number Format General, the value “001010”, and you will see Excel stores this as 1010, since it determined your value to be a Number. Of course we wanted it to be the equivalent of a NAV Code field, and expected Excel to keep it like “001010”. In order to do this you can for the field tell it is text by adding a ‘ in front. So far so good….

But when doing a copy/paste you dont have that option, so what to do?

Before doing your copy paste set the specific column, or the whole Sheet to Number Format = Text. Then the values you paste in will stay formatted the way exported them from NAV.

Not really a NAV trick, but related. And just learned this today, as a customer was getting audited and they needed the data out to Excel.