none
Excel not formatting numbers properly with default number group separator RRS feed

  • Question

  • Hi all,

    I'm a developer for a small business. I'm experiencing this problem with one of our clients.

    When the computer has the regional settings "Dutch (Belgium)" - "nl-BE" (or "Nederlands (België)"),  the default number group separator looks like a whitespace. When numbers are exported to an excel file via our Windows application, it formats the numbers as strings. Not as numbers..

    It turns out that the default number group separator is not a whitespace but consists of the ASCII characters 194 160. Excel seems to be unable to parse the text as numbers with those ASCII characters as separator. The ASCII characters are present in the outputfile. But the numbers are interpreted as text.

    When I change my regional settings to another setting but modify the default number group separator into the same ASCII characters 194 160, Excel can't interpret the numbers as such either.

    The path the application follows is in short this:

    1. Numbers are in a datagridview.

    2. All records are selected and copied to the clipboard.

    3. A new excel file is generated with one worksheet in which the data is pasted.

    On our development stations we have been able to workaround the problem by changing the default number group separator in an actual whitespace. But with this client we're not allowed to make that change.

    My computer runs on Windows 8 EE with all the latest updates installed. The application is developed in Visual Studio 2012 in .net framework 4.5 with Office 2013 for home use and small businesses.

    Best regards,

    Jan Vermeer

    Friday, February 6, 2015 12:47 PM

All replies

  • Hi Jan,

    According the description, you were developing an applicaiton import data into Excel worksheets. And it formats the number as strings.

    Would you mind sharing some code snippet about how you import the data?

    And as far as I tested, when I copy the data from one worksheet to another, the data copy as numbers correctly. Here is the code:

    Sheet2.Range("A1:A3").Copy
    Sheet3.Paste

    I suspect there maybe incoorect when the data copy into clipboard. As a workaround, we loop the data and set the value cell by cell.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 9, 2015 2:34 AM
    Moderator
  • Hi Fei,

    thank you for your reply.

    The datagridview is populated using this (where fields is the table to be assigned as datasource to the datagridview):

    fields(j, omega - 1) = CDec(testSum).ToString("N")

    Then it gets copied to the clipboard from which it gets pasted in Excel:

    Dim app As New Excel.Application()
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    
    wb = app.Workbooks.Add
    ws = wb.Worksheets(1)
    
    dgv.SelectAll() 
    dgv.ClipboardCopyMode = If(ExportHeaders, DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText, DataGridViewClipboardCopyMode.EnableWithoutHeaderText) 
    Clipboard.SetDataObject(dgv.GetClipboardContent) 
    dgv.ClearSelection() 
    ws.Paste(ws.Cells(rowindex, 1))

    I don't think however there is a problem with the application. I checked an Excel output file generated by the application and copied a cell which contains a number > 1000 into an online text to ASCII converter, it clearly showed the ASCII characters 194 160 as number group separator. So Excel should be able to interpret this as numbers. I then looked into our application and found the ASCII characters also in the datagirdview and in the text copied on the clipboard.

    If I change the number group separator into a string (space, comma, point,...) the generated Excel file is perfect. When the number group separator is some invisible ASCII chars, it is not ok.

    Best Regards,

    Jan Vermeer




    • Edited by Jan Vermeer Monday, February 9, 2015 1:33 PM
    Monday, February 9, 2015 1:14 PM
  • Hi Jan Vermeer,

    Thanks for the detail explaintation for this issue.

    >>If I change the number group separator into a string (space, comma, point,...) the generated Excel file is perfect. When the number group separator is some invisible ASCII chars, it is not ok.<<

    Is it helpful, when you format it as numbers manullay? If yes, as a workaround, we can Recrod Macro to get the code to simulate the manual operion.

    If not, woulud you mind providing a sample spresheet to help us understand this issue exaclty? You can upload it via OneDrive and please remove the  sensitive information before you uploading.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 10, 2015 7:23 AM
    Moderator
  • Hi Fei,

    Thank you for your time and effort. I have created an xlsx document example that illustrates my problem. My regional settings for this example are "Dutch (Belgium)", with every setting set to its default and as location i have selected Belgium.

    file: https://onedrive.live.com/redir?resid=EFEF3F0FCDB3F9C4!106&authkey=!ALn5eN9pWck8rCU&ithint=file%2cxlsx

    In cell B1 I have the number as such it is generated by the software.

    In cell B3 i have the same number formatted manually in Excel.

    When I copy paste the cell B1 in this online text-to-ascii converter it shows the 194 160 ascii characters that is set as my default number group separator.

    However when I copy past the cell B3 in the same online converter it shows 032 as number group separator although my settings specify a different one.

    converter: http://www.unit-conversion.info/texttools/ascii/

    Sorry for the links as text, I'm not able yet to post links on this forum.

    Best Regards,

    Jan Vermeer


    • Edited by Jan Vermeer Tuesday, February 10, 2015 9:11 AM
    Tuesday, February 10, 2015 9:09 AM
  • Hi Jan Vemeer,

    Thanks for the detail explaintation for this issue.

    Since the issue is complex, I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.
     
    Sorry for any inconvenience and have a nice day!
     
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 12, 2015 1:35 AM
    Moderator
  • Hi Fei,

    I will wait for your response. It is somethnig small, but it is important for our client. I will check this thread regularly.

    In the mean time a big thanks to you for everything you're doing on this subject. I really appreciate it!

    Best regards,

    Jan Vermeer

    Thursday, February 12, 2015 1:16 PM
  • Hi Fei,

    can you give me an update on this investigation?

    Thanks!

    Best regards,

    Jan Vermeer

    Monday, February 23, 2015 12:32 PM
  • Hi Jan,

    Please check if the following helps here:

    Creating Office Solutions for Use in Multiple Countries/Regions
    http://msdn.microsoft.com/en-us/library/aa168494(office.11).aspx#odc_vstmultcr_topic04

    Excerpts:

    Whenever a managed client calls a method on a COM object and it needs to pass in culture-specific information, it does so using the CultureInfo (locale) that matches the current thread locale. The current thread locale is inherited from the user's regional settings by default. (All late-bound calls and calls to members with LCIDConversionAttribute set implicitly pass the default System.Globalization.CultureInfo.CurrentCulture property value.) The success of the call depends on the availability of the files that Excel requires to support the locale supplied by the caller.

    Supporting files for a locale are available to Excel if:

    • The locale matches the language version of the Office installation, or
    • The locale is English United States (en-US), or
    • The locale matches an installed MUI Pack.

    If you continue facing issue for your specific scenario, Because of its complexity your question falls into the paid support category which requires a more in-depth level of support.  If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone





    Sangeeth,MSFT

    Monday, February 23, 2015 7:09 PM