Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Change Vba Decimal Separator

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes

Similar Topics







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 am trying to import a csv file using Ms Query because I want to process the data with SQL before the import.

When I am at the mask "Define Text Format" I am getting an error "Text file specification field separator matches decimal separator or text delimiter. Guess failed". This happens because I have a comma as a decimal separator. I tried to google for a solution but didn't really find one (except changing regional setting with VBA which I am not too keen to do). Shouldn't it be an easy task to import csv files? They are pretty common and many countries have comma as a decimal separator...

Any ideas?? Thanks!

Jack


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


Hi Folks,

I have problem with data type.
I use data in worksheet from url location and all values which are updated come with
comma separator (polish decimal system use comma instead of dot as separator),
I use in my excel english decimal separator which is dot.
Now I would like to replace commas with dots but the problem is that data are constantly
updating so every time when that happen I have comma in updated data.

Can anyone advise or help me with macro or function please?


I have written a Matlab program to do some data analysis. The results are then exported to an Excel-file. I'm am planning to distribute this Matlab-script among my colleagues. However, some of their computers will use the point as a decimal separator (English system locale), whereas others use the comma (Dutch system locale). Matlab exports its data with a point as decimal separator, which gives incorrect results if the program is run on systems with the comma separator.

I have noticed that numbers with decimals, in an Excel-file created on a system using a decimal point, are automatically converted by Excel when the file is opened on a system with a decimal comma (so 2.56 becomes 2,56). So the Excel-format must include some information on the decimal separator or locale of the system at the last save (otherwise Excel wouldn't know that it had to convert the numbers). I am wondering whether I can set this decimal separator or locale myself for the exported Excel-file? This way, an Excel-file created by Matlab on a system with a comma as separator will be recognized as an Excel-file that was created on a system with a decimal point. Then Excel should automatically convert the numbers.


Hi!

When I type a number in my userform using comma as the decimal separator, the value returned in my excel sheet is stored as text. How can I avoid this and return a value that is stored as a number with comma as the separator and with one decimal ? I've tried to use the format function, but I keep getting the wrong format.

When I use:

ws.Cells(iRow, 7).Value = Format(Me.txtInnveid.Value, "00,0")

..the output ignores the typed number after the comma.

(Btw: My system settings use comma as the decimal separator.)

Any help would be appreciated!

-Ole


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?


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 ".\htdocs\excelfile.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,

Is it possible to open a csv-file with a number of columns that are
separated be a certain separator in Excel, so that Excel puts the text in
the right columns automatically by looking at the separator. I want Excel to
do this without using the wizard 'Text to Columns'. This works fine for me
when I use the semicolon as separator. I presume this is the default
separator. But how can I change this separator to for example a comma, so
that Excel can also open my comma-separated files in a correct way
automatically?

I've already tried to change the 'List Separator'-setting in the 'Regional
Settings' to a comma, but this doesn't work. Does anyone know the answer
please?

Thx,

Lieven





Hello,

Is it possible to open a csv-file with a number of columns that are
separated be a certain separator in Excel, so that Excel puts the text in
the right columns automatically by looking at the separator. I want Excel to
do this without using the wizard 'Text to Columns'. This works fine for me
when I use the semicolon as separator. I presume this is the default
separator. But how can I change this separator to for example a comma, so
that Excel can also open my comma-separated files in a correct way
automatically?

I've already tried to change the 'List Separator'-setting in the 'Regional
Settings' to a comma, but this doesn't work. Does anyone know the answer
please?

Thx,

Lieven





Hi All

In my office Excel is set to use commas as the decimal separator, however some software we use for recording data outputs with a dot as the decimal separator.

Is there a method by way of a formula that will allow me to change the dot to a comma? Until now I have been changing it using the Find and Replace function, however this is now a protected workbook on our file server and this function is no longer allowed.

Thanks for all suggestions

Jonathan


Hello everyone.

I have a problem with my excel set up.

Any data I import where the value is >1000 and contains a comma separator gets converted to text ??

I looked at my settings on Tools>Options>International and:
I have a "." separator for the decimals,
The thousands separator is blank? I tried to key in a comma but it won't allow entry in the field.

Does anyone know how to cure this problem ?

thanks in advance


Can I do this?

The default format is 2 decimal digits, and no separator for thousands.

I have found that I am adding the thousands separator far more often, than removing it, so it would be good to have it set like that by default.

Thanks.


hi,

in my settings of excel is use a "." as decimal separator.
however, when i use the following little sub, a comes out as 1315.
how is this possible?
my system settings use a "," as decimal separator, but VBA should use the excel settings, right?

Sub testdec()
Dim a As Double
a = CDbl("13.15")
End Sub

regards,
jan


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




Hi,

I have a problem with Like operator that should compare values containing number.

I have a textbox that user enters value in. Then value is formated as "#0.0 kg".
After value is updated I would like to cheque if it is like "##.# kg" and if so get the number out of it.

But is dose not work, probably because decimal separator is set to coma "," in application properties, and in consequence value of a textvbox is like ##,# kg.

How can I avoid this problem no meter what would be the setting for decimal separator in application properties.

Cris.

Enter 7.3 in two cells, first with 7.3 as is the american standard, and then with 7,3 using comma as decimal separator, which is most common in Europe.
Then use value() formulas referring to those two cells.

If Excel is set to use . as decimal separator, value() will return the #value error on the 7,3 but correctly handle 7.3. If you change the settings in Excel to handle , as decimal separator, it will return the #value error on 7.4

In this case, it seems that the 'internationalization' of Excel breaks the formulas, rather than help things work in spite of different standards.

I made the above example as simple as possible. What I actually do is getting various data with web queries. Thus one thousand will have the format 1,000.00 on some web pages, and at other times look like 1.000,00. To enforce a single number standard on the web is beyond my powers. My present solution is to convert the data with a bunch of very unappealing string operations.

Somewhere in Excel is maybe buried a way to tell it that some data have one form and some another, but I can't find the solution, and would be happy for suggestions.


I'm running a program that exports information to Excel. It uses a space as a thousand separator, and a comma as a decimal separator. Is there a formula, or way to amend this so that Excel reads it as a number?

I've tried changing the settings in Excel Options -> Advanced -> and changing the separators, but Excel still reads it as text, rather than a number.

I'd really like a formula (or formulas) to amend this. I've tried the TRIM function to take out the space, but this doesn't seem to work...

Thanks a lot!


I would like to format cells so the numbers are displayed like the general format
but with the thousands separator. I can't figure out how to insert the thousands
separator without fixing the number of decimal places.

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

I would like
=10
to display as
10 .

Thanks for any hints.



I have a set of data in Column A1 to A100

i need a macro to replace the comma separator to a dot but at the same time, dot separator to a comma separator ?
but if the number has only comma separator or only dot separator no action should be taken

for example
1,765.12 should be like 1.765,12

but
if the number contains only dot or only comma no action should be taken
for example

1765,12 number should not be changed




please help


I'd like to format a worksheet with all of the numbers having a thousands separator but only displaying a decimal point if it's needed. Can this be done?


Hi,
is there an automated solution for the conversion problems between "," and "." as decimal point. I use "," as decimal point and "." as thousand separator. I made a small macro to replace "." with ",", but I have one problem I still can't solve, just as I bring data from a query in a sheet, small values (like 5.00, 7.00 and such) change to date format and replacement doesn't solve the problem with them. I'm sorry to ask a question that seems to be very frequent, but I couldn't find an answer (a VBA that doesn whatever it needs that I can switch a worksheet or a workbook between two notation systems ("." and "," as decimal point, "." or "," as thousand separator)


I have a problem when I read the ":" colon as a separator from an excel or
text file that uses it as a separator.

the example I have is that when the colon separator is followed by a
number, say for example : 35, the number appears on the cell as 35:00:00
which is a time format.

It seems that Excel takes the separator as a time format for the cell and it
does not allow change of the cell's format not even to a simple text format.
Even when the formatting of the cell is changed to a general format it
generates a decimal number that is a multiplier of 24. If the value in the
cell is ": 24" and we format the cell as text it would show a"1" in it.

Is there a way the maintain the cell as a text formatted cell where the data
is maintained the same even if a colon is used as a separator?

In other words if ": 24 " is entered the cell should stay as ": 24" after a
text format is applied.





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.


Hi,
I have a macro which uses a date entered in a cell to be used as the sheet name.
It works OK as long as the date separator is "-". But in some machines it is "/" and then the code fails as sheet names cannot have "/" in it.
How can this problem be solved other than manually changing the date separator from Control Panel>Regional and Language options>Date>Short Date>Date Separator (in Win XP)

TIA.