When exporting f.ex Customer Ledger Entries to Excel via the button in the menubar (CTRL+E) all numbers are imported as text. I have tried changing the format of the whole column to Number but this does not help. (only a few will change)
Can anyone tell me how to get number-format in NAV to be number-format in Excel when exporting?
I know that you can change the Group Digit symbol to . in Windows/Regional and Language Options, under both Numbers and Currency but this will only have an effect when copying from NAV and pasting in Excel. This function does not inflict export to Excel via button.
What country version of NAV 5.0 SP1 are you using? I am asking this as for various countries (a.o. Finland, Sweden and Switzerland) a fix had been implemented to get this working OK.
For me using Dutch regional settings and using whatever NAV 5.0 SP1 version the Excel export is working OK.
Standard Norwegian is comma as decimal separator and dot for thousands separator.
And as I explained earlier, when copying from NAV and pasting in Excel this works and all numberformats stay as numberformats. (as long as this is set in the Regional and Language options) But export via button works not.
So same separators a we (Dutch) are using. As said above: “For me using Dutch regional settings and using whatever NAV 5.0 SP1 version the Excel export is working OK”.
At this point I do not have any other ideas/hints. [:S]
All this mess with thousands separator comes from fact, that Navision for unknown reasons uses all settings from Regional, except thousands - there is an unprintable character instead, shows sometime up as a square, but not in Excel.
The problem comes from Native DB, and it SHOULD vanish on SQL, but it does not [:@]
And the worst is, that I have had a situation, that it still works, but not on ALL workstations, and we couldn’t find the reason - same client, same Win settings, even the Excels we synced to one version…
We wrote an small Excel macro then, that does ReplaceAll with this invisible char to nothing…
Luc, you are far more closer to Navision design than me - maybe you still have some ideas why this was done?
I have encountered this issue using the Export To Excel function on Account Schedules on NAV 2009 R2 W1 version. It inserts an invisble character ASCII 160 as the thousands separater.
This explains why Excel does not treat the data as number, or , at least, general. ASCII-160 is “non-breakable space” character, hence Excel supersmart autoguess what was inserted in a cell treats the whole data as text.