Weird decimal separator issue

[joebanana]

I have an Excel file that is generated from an SQL db, through an OpenXML request.
All fields are exported as strings.

Depending on the customer's configuration, sometimes the decimal separator is a comma, sometimes a point.

When the Excel file is first open, some code runs to clean and normalize the data, especially to convert numbers and dates properly.
I check for the local decimal separator (from the regional settings) and replace whatever separator is used in the file with it.

This is where I have a problem.

Let's assume my separator is a comma.
If the file already has commas, then it's fine, nothing changes.
If it's a point, sometimes it works, sometimes not.

If the number I want to convert has 2,3,4 decimals, it converts fine.
More than that and it goes crazy.

A number like 32.12345678 will be converted to 3212345678 (the point is not replaced, it is even removed!)
The same number but 32.12 will be converted to 32,12 just fine.

This is driving me crazy. By the way I used the replace function to change the string in complete ranges, much faster.

Any help is appreciated!!

[Leith Ross]

Hello joebanana,

Welcome to the Forum!

I am guessing your forgot the step of disabling the System Separators after you changed Excel's. Here is how the code should look...

Please Login or Register  to view this content.

[joebanana]

Hi Leith,

Thanks for your reply. Indeed I am not changing Excel's separators.
Where exactly should I put the code above?

At the beginning of the procedure?

Can I set UseSystemSeparators back to true when the procedure has completed?

Thanks again, very appreciated.

[Leith Ross]

Hello joebanana,

You can place the code at the beginning of the procedure and yes, you should set the SystemSeparators to true when you are done.

[joebanana]

Unfortunately it is still the same.

Maybe I can explain better... below is an example of the data I have:

Column A
3,03
3002,45

Column B
3,0303030303030303
3002,459868482784723

The decimal separator is the same, as you can see.
When I use the replace function

Please Login or Register  to view this content.

This is where it fails. There is no . in the cell, yet Excel somehow removes it, thus converting my data to something like 3030303030303.
It works just fine in the column where I have 2 decimals.

So it has something to do with the numbers of decimals...

I'm puzzled, really.

[Leith Ross]

Hello Joe,

Can you post a sample of the data and provide some before and after examples? This will clear up a lot a questions for me quickly and get you the answer you need.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[joebanana]

There you go.

To have the code run, change the value in the Settings tab from No to Yes.

Thanks again!

[joebanana]

By the way here is the source data if you need to "play" with it.

[Leith Ross]

Hello Joe,

Thanks for posting the workbook. Since you are in Quebec, you're dealing with both English and French number styles. Are you also running English and French versions of Office? Will the macro be used by both?

[joebanana]

That's an annoyance indeed.

My file needs to be compatible with Excel 2007 and 2010, English and French.
This is why the decimal separator is not always the same when the data is output from the SQL database.

Also, I MUST display the data, once cleaned, in the locale format.

[Leith Ross]

Hello Joe,

That's what I thought. I will need to make some changes to the macro to detect the Excel version and the number formatting.

[Leith Ross]

Hello Joe,

You're right, this is crazy. There must be something causing the inconsistent behaviour when the data is either imported or cleaned. I have not been able to track down the cause yet. I have changed the Application settings, the Regional settings, examined individual cell formats, and nothing obvious stands out as to why some cell formats change and some don't.

[Leith Ross]

Hello Joe,

Found it! It is the importing causing the problem. I verified it by using the CELL worksheet function. Setting the Info_Type string to "type" reveals that some cells are Labels. Although they look like numbers have a General format, Excel regards them as Text!

For example, using the "Data" sheet, cell K2 will return a "v" (Value) and cell O2 will return "l" (Label). This why some cells, the ones with values, change and other remain the same. Good news is the macro can check the cells and convert them automatically and set the number format according to the locale.

[joebanana]

Leith,

I'm home now and won't be able to try again until Monday, but I really hope you're right.

You are mentioning Excel reads the cells as Text, but this is correct. Well, not correct, but known.

The Excel file is populated from an SQL db using OpenXML, and OpenXML writes everything in Excel as STRING.
My Excel file is pre-formatted, meaning I already formatted my columns to whatever they're going to be eventually (a number, a date, a text).

Normally, both columns that show Power Plan Usage should be set as number.

Either way, does it explain why Excel removes the comma when I ask it to replace a point?

[joebanana]

I managed to resolve the issue by having my DBA modify his OpenXML routine to export the fields the way they should.
They are now directly recognized by Excel and I no longer need to waste my time converting them.
Thanks all for your comments!