When text is converted to number, strange things happen

[lyrklaunavan]

Hi,

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. What causes this can you help me? I have Excel 2010 loaded.
The details are in below picture.

http://c1110.hizliresim.com/q/r/3j0p.jpg

 

[p45cal]

It looks like you're using Excel with a locale setting that uses the full stop (period, dot, whatever you want to call it) as the 1000's separator; if you're not used to this, maybe some macro code has changed this setting for you?

Taking the 1607.947 reported as a number stored as text (it may well have started off life as a number with a decimal point somewhere else, but it's text now). The equivalent string in US/UK settings might be 1607,947 ; if in the UK/US locale you convert that to a number it clearly becomes 1,607,947 which is 1 million, 6 hundred and seven thousand, 9 hundred and forty seven. If you choose to miss out the 1000 separator by unchecking the box Use 1000 separator you would get 1607947
Just the same thing happens with a full stop in a locale where the full stop is a thousands separator (rather than a decimal point).
Incidentally, looking at your jpg, it seems the a full stop (a .) is the thousands separator when you look at what's between the parentheses in the Format Cells dialogue box, (look next to Use 1000 separator). On my machine, which uses the UK locale, it's much more obviously a comma (a ,).

 

[lyrklaunavan]

Thanks for the reply. I will try to change thousands separator as comma from options tomorrow and see what happens. My settings are (From Excel-Options-Advanced Tab) Use system separators is checked. Decimal separator is comma and thousands separator is dot.

You are right about this excel data comes from as output of a program. 1607.947 is the first output. According to my settings above dot is the thousands operator and this is 1 million 6 hundred and 7 thousand and 9 hundred forty seven. But this is an absurd output for my job. It has to be 1 thousand and 6 hundred and 7. I think the program is giving wrong output. I have to search that also. It seems excel is right.

But if above is true, then on the picture what does 1.607.947 mean? I have no idea.

In additiıon to that the number below 1.607.947 did not change when I erased two decimal places.

1.607.947 turned to 1607947
1450.86 did not change !!!

This is disgusting

 

[p45cal]

I will try to change thousands separator as comma from options tomorrow and see what happens. My settings are (From Excel-Options-Advanced Tab) Use system separators is checked. Decimal separator is comma and thousands separator is dot.

You'll have to change BOTH thousands separator to comma (or nothing) AND decimal to a dot. This last is vital.

You are right about this excel data comes from as output of a program.

Does the programme produce a file (a text file?) first, which you then import somehow into Excel? How do you import the data into Excel?

1607.947 is the first output. According to my settings above dot is the thousands operator and this is 1 million 6 hundred and 7 thousand and 9 hundred forty seven. But this is an absurd output for my job. It has to be 1 thousand and 6 hundred and 7. I think the program is giving wrong output.

I think the programme is giving the correct output for the UK/US settings. Excel too is behaving, but needs its setting to be right.

In additiıon to that the number below 1.607.947 did not change when I erased two decimal places.

1.607.947 turned to 1607947
1450.86 did not change !!!

I need to see how this data gets into Excel before answering that one…

 

[lyrklaunavan]

Hi p45cal,

I ınterchanged comma and dot in Options and everything is working flawless now. Thanks for the help.