Free Microsoft Excel 2013 Quick Reference

decimal separator is comma, not period

This is driving me crazy in Excel 2010.

I can't get cell formatting to display a period to separate dollars and cents (I formatted cells Currency, with two decimal places). Only happens when I'm using the number pad on the keyboard. No other program does this, anywhere. So the keyboard is working right.

When I insert 6.80, it Excel displays 6,80 in the cell address data line, then shows 6,80 as the amount.

Also cell contents does not show $ sign even though cell is formatted Currency.

I'm about ready to reload Excel 2003!

Any help would be appreciated.


Post your answer or comment

comments powered by Disqus
Greetings.

Concatenate function seems to have a bug in Excel 2003 when same sheet
is displayed on a different locale.

For example two cells contain
A1 A2
Hello 10.00%

CONCATENATE(A1,";",A2)

yields

Hello;0.1 (with dot)

and

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0.1 (with dot)

When I send this sheet to Russia where decimal separator is comma not
dot

CONCATENATE(A1,";",A2)

still yields

Hello;0.1 (with dot)

but

CONCATENATE(A1,";",FLOOR(A2,0.1))

yields

Hello;0,1 (with comma)

And there are VLOOKUPs in the sheet, and they all fail because of this,
since CONCATENATE is used to create compound-key field for VLOOKUP.

Any suggestions?

Thanks

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!!

I am developing an Excel application to be used in European countries that use different decimal separators (comma or period) and different 1000 separators (period and comma). Therefore I need a system call from VBA that can tell me which decimal separator is actually used.

In other words, how do I in VBA ask the system for the decimal separator?

How to change my Mac exel so that it does not use dots as decimal
separator and commas as separator for thousands? I would require it to
be the other way a round. As you can imagine it is a litle annoying
when the excel does not understand the local way of handeling numbers.

Hi!

I have tried to use a combobox in selection between values which have a
decimal part. As I'm European I use commas as decimal separators. However
although in the selection list of combobox the decimal separator is comma,
the chosen value in the linked cell is written with a dot and so I cannot use
the value as as a source value for calculation. Why is this happening? Why
does the combobox change the decimal separator? The weirdest thing about this
is that sometimes the combobox has returned the right value with a comma as
decimal separator.

Can anyone help with this?

Thanks in advance,
Erkka

Hi,

in my company we output csv-formatted files from intranet service and open
them to Excel. We all have Excel version 2000.

In some machines the files open correctly: All the data is separated to
different columns. In some machines when we open the same file, all the data
is put only to one column.

If I first save the csv file to harddrive and then open it to Excel, the
data is separated correctly in all machines.

I have checked out regional settings in each machine. They are right. Our
office is in Finland so our decimal separator is comma as well as our list
separator is comma.

In those machines where the csv doesn't open correctly when opened by
Internet Explorer from intranet, I tried to change the decimal separator to
period (.) and kept comma as list separator. After that the csv files started
to open correctly.

Is this a XP service pack 2 problem? I think in those machines that open csv
correctly don't have XP service pack 2 installed. In those machines that csv
doesn't open correctly they have SP 2 installed. The Excel versions and
service releases for that are the same.

Hello, we need to deal with different International Decimal Separators all the time, US and Abroad. The US uses the traditional period ("."), while the abroad files uses comma (",").

To change from one to another we go to Tools, Options, International and click or unclick the "Use the System Separator", while the decimal and thousands separators are already setup for the abroad system.

The problem is that Excel does not save this information within the xls file. So, opening any xls file, no matter its requirement for decimal separator, Excel will always use the last setup, no matter if this setup is the one for that file or not.

This cause a complete havok with our international and national documentation, since we always need to pay attention (and lots of it) if the separators are in accordance to the file being edited and printed. You can imagine how much printer paper we waste printing files with wrong separator setup.

Any way to save the setup with the file, or include a macro or auto-script with the file that automatically change this setup when the file is loaded?

Thanks for any help, we really appreciate it.

Wagner

I use a Norwegian version of Excel 2007, but has set the decimal point to be period, not comma (default is comma). However, when I make a chart, commas are still used as decimal points.

Does anyone here know how I can change that to period? Or is this a bug in 2007?

Greetings to all -
I created a workbook using the US version of Excel Mac 2001 on MacOS 9.2.2 US. I later had to access the workbook using the exact same copy of Excel but running MacOS 9.2.2 Swedish version, and all my decimal separators were changed from '.' (point) to ',' (comma). I tried reformatting the output by defining a new custom format (0.00) but that did not work on negative numbers (MS found an error in the formula - even when there is no formula - just a display). Changing the settings of my Mac to use the decimal point separator did not help. Can one detach the behavior of Excel from the OS platform in the particular case?
Many thanks
Jamur

Hello,

This is related to userform for excel.

I am trying to perform some multiplcation on text1, which takes numbers provided by the users. It works fine ONLY IF users enter integer.

If the number entered contains decimal, the calculated result rounds up to an integer unconditionally.

If the number entered contains thousand separator, the calculation would not work.

I have lay down validation IsNumeric but that would not prevent users from using thousand separator or decimals.

Restricting the input as integer is not desirable though.

So the question is,

- is there a way to convert the field type of the textbox to a number instead?
- is there a way to restrict what users can enter? ie. without thousand separator
- ultimately the form would be used for users reside in different countries, so it would be great if i could accomodate thousand separator (like comma and period).

Any suggestion would be greatly appreciated.

How can I see the text separate with column, not comma. When I open the same
file with another computer, it is separate with column. It has probably
something to do with my computer.

Hi all,

In text files, tab is used as column separator and comma is used as decimal
point. Numbers are like 1,4451, 0,8, etc. The OS is windows 2000 and office
version is 2000. Regional was set to Dutch(Belgium). When i double click one
file, the file was opened in excel but with wrong data. 1,4451 is shown as
14,451 in cell. And its real value is changed to 14551 by excel.
However, if i use import feature to open the file, every thing is okay. Why
excel does not use system's regional setting to open a text file?

Thanks

I have a web application, and it will create report files with EXCEL 2003 xls format.

My web application is using Apache/2.0.52.

My EXCEL 2003 is Germany Version(Europe use "," as decimal separator).

My created xls files have numeric fields which use COMMA as decimal separtor.

The application provides links to those excel files for users to download.

The screenshot looks like sth as:
exceldownload.jpg

The browser is IE8.0.

The issue is:

When I either open the excel files direct through IE8; or right click and "Save as" to my local disk and open it, the decimal separator will change from "COMMA" to "PERIOD".

But on the web server directory ".htdocsexcelfile.xls", I open this file directly, it is still European format(COMMA as decimal format).

If I use Firfox 3.5, there is no this format automatically switching issue.

So does anybody know why IE8.0 change my Excel decimal separator setting from "COMMA" to "PERIOD"? How should I fix it?

And I am also wonder how IE8.0 is working with EXCEL when I open Excel file through IE8.0?

Any suggestion will be appreicated.

Hello.

I want to format cells with custom number formatting, so that it displays 2 decimal places after separator, but only f it is not whole number. It has to look like this:
3,4578 > 3,46
3,00 > 3
If i use 0,## number format, it leaves decimal separator after the whole number:
3,00 > 3,
How can set format, so it doesn't show decimal separator at the end of a whole number?

P.S. I'm using comma (,) as a decimal separator.

Thanks.

I have two tables:

First table M24:R37 has two columns, one column has single values per cell (K24 has A; K25 has B, etc.) second column has multiple values per cell (M24 has Z1, Z2, A1; M25 has B1, B1A, B1B; etc.)

Second table has one column of single values per cell (A1, B1, etc.) starting in cell A5. Second column is blank and would like to be populated with a corresponding value from column 1 in table one (M).

What I would like to do is to create a Vlookup type command that will look for a value in second table Column A from the first table M24:R37 and return value from first column (M) in that table. The issue I am having is with the values in column 2 of table one that have values separated by commas. I do not think excel is recognizing that it is a list of individual values.

A1
B1
B1A
B1B
B1C
etc.

A - Z1,Z2,A1,B5
B - B1,B1A,B1B,B1C
C - D1,D1A,D1B,D1C
D - C1,C1A,C1B
E - D5,D5A
etc. - etc.

Questions
- Is there a way to change the decimal separator of VBA?
- Is there a way to define the decimal separator of a Listbox?
(Like you can define in Excel that it should use either a dot or a comma as separator)

Background info & Problem
I use inputboxes and some forms to have the user enter some doubles. However, as all numbers are returned as text, 0.022 becomes "0.022". When I now cast in VBA this string to a double, it becomes 22.

Tried Solutions
I tried the following solutions:
- Setting the decimal separator in Excel options>international to " . ". (Here in Belgium, we normally use " , " as a separator.)
- Replace (String, " , " , " . ") and Replace (String, " . " , " , ")
- Format / Numberformat "#.##0,00", "#,##0.0#" and so on (trial & error they call this)
- Setting the language that controls the default behaviour of the office applications to English (UK) instead of Dutch.

Of these 3 methods, only the "replace"-method gives sometimes good results. Why only sometimes? Because the listboxes, where the number are stored, sometimes use a " , " as decimal separator and sometimes a " . " as decimal separator... Why this inconsistency? I have no clue.

Excel Info
I use Excel 2003 SP3 on Windows XP. It is an English version of Excel.

(So, it is an example of the classical separator problem, as discussed in many threads, only I haven't found a solution that works for me)

Any help is highly appreciated.

Many threads discuss the Decimal Separator problem, but I have found none which discusses it for listboxes.

Does anyone know what determines the decimal separator for listboxes? Or how VBA can read what it is or even better, sets it to a certain standard.

Because my problem is the following: My sheet contains some listboxes and sometimes it uses the European decimal separators "," (my default), sometimes it uses the American decimal separator ".".

I've been trying to find some logic in it through trial and error, but I have found none! Switching in Tools>Options>International does not affect the listboxes. Yesterday the listboxes used a ",". Today, they suddenly use ".". So you would think that switching decimal separators, saving the sheet, closing Excel (not just the sheet) and restarting Excel, might do the trick, but it doesn't.

And if it was just a matter of layout, I wouldn't be bothered that much. But VBA tends miscalculate (wrong input: e.g. 48 instead of 0.48 or 0,48 as you wish) and sometimes even throws errors as soon as a wrong format is used (casting the string .48 to double doesn't work if it use "," as separator).

Any help will be highly appreciated.

Kind regards,
Maarten

I import an array of data from a text document to Excel; the array contains only numbers.
The problem is that if a number has exactly three digits after the decimal the decimal separator is considered to be a thousands separator; thus 1.446 (one point four four six) becomes 1446 (one thousand four hundred forty six.

Control panels regional and language options is set to decimal comma and point for thousands separator and that is fixed as it is a company owned computer with only IT-dept. access to such things. However, Excel operates with decimal point. I don't know this is set up.

NSV

I am trying to save an Excel (2003 or 2007) file as a semicolon delimited text file and have encountered two issues. First, the only way I found to save the file as semicolon delimited was to change the international options to make the decimal separator a comma, then save the file as a CSV. This ruined my data though because it changed all my periods to commas, which is unacceptable.

Second, and most importantly, some of the numbers in my files have up to 13 decimal places. When I save the file as a text file, these numbers get rounded down to 8 decimal places. I tried changing the fixed decimal places to 13, but that didn't change anything. Any help would be appreciated. Thanks

I have a spreadsheet that was created with 6 columns, and now after being saved as a csv file, all the data is in 1 column separated by commas. Is there an easy way to convert back to 6 columns?

Thanks

Hi,
I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

Is there any way to accomplish this?

P.S. Unfortunatelly I am not allowd to use macros to solve this issue.

Thanks a million.

I routinely have do download massive data sets of reporting that is saved as an excel spreadsheet. The three columns of sample data attached are Report Numbers, Report Evaluation Serial Numbers, and Report Evaluator ID.
As seen in the attached spreadsheet, there can be multiple Report Numbers (in same field separated by comma) which have been evaluated by different Evaluators. It is my responsibility to account for the number of Reports that have been evaluated, and many other metrics from like data. The issue I have is when more than one report number is listed in the same field I need to parse the data into its own field for ease of counting (and also maintain the adjacent data). It is not a problem to merely copy the fields and delete the excess numbers, however when dealing with thousands of Reports, and Evaluators this can be very time consuming. In the attached file I have separated .xls into three workbooks to help explain my problem: Initial state of Data; What I need To Parse Out; and The final endstate I require. I hope that I have explained this issue with enough detail. I am sure that the attached file will explain better.

Any help in this matter will be greatly appreciated!

I have two tables:

First table M24:R37 has two columns, one column has single values per cell (K24 has A; K25 has B, etc.) second column has multiple values per cell (M24 has Z1, Z2, A1; M25 has B1, B1A, B1B; etc.)

Second table has one column of single values per cell (A1, B1, etc.) starting in cell A5. Second column is blank and would like to be populated with a corresponding value from column 1 in table one (M).

What I would like to do is to create a Vlookup type command that will look for a value in second table Column A from the first table M24:R37 and return value from first column (M) in that table. The issue I am having is with the values in column 2 of table one that have values separated by commas. I do not think excel is recognizing that it is a list of individual values.

A1
B1
B1A
B1B
B1C
etc.

A - Z1,Z2,A1,B5
B - B1,B1A,B1B,B1C
C - D1,D1A,D1B,D1C
D - C1,C1A,C1B
E - D5,D5A
etc. - etc.

I imported decimal data into excel,

unfortunately the decimal separator is . which excel does not recognize. How
can I convert it into ,

Kris


No luck finding an answer? You could always try Google.