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



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 Tutorials

Round to a Specific Decimal for Any Number in Excel
Round each number to a specific decimal number. This works for numbers that don't already have a decimal and ones t ...
Get the Decimal Part of a Number in Excel
How to get the decimal part of a number without any rounding in Excel. This tip allows you to simply remove the who ...
Show Fewer Decimal Places Without Losing Precision in Excel
How to show a smaller number that looks better without sacrificing decimal places or precision in the actual number ...
Simple Excel Function to Combine Text with a Separator
Excel function that makes combining text very easy. This function is simpler and better than the CONCATENATE functi ...
Round Numbers Up or Down in Excel
How to round a number up or down and also to a specified number of decimal places in Excel.  This will allow you to ...
Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...

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







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.


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


On my indows XP box, when I type a number into an Excel spreadsheet and it is in "General" format, the numbers appears like this (examples):

1234567

or this:

7.125

or this:

8.1

I want them to look just like that except for the first one, which I want to have the thousands separator:

1,234,567

I don't want to use a number format because
Excel makes you set a specific number of decimal places...I want the decimal places to float depending on how many decimal places there are in the entered number (just like the "General" format does). So basically, I want a "General" format, but with thousands separators (commas).

I tried using something like ###,###.### but that leaves a decimal place at the end of each integer (i.e. "123,456.") which is also not acceptable.

Does anybody know 1) what I am talking about, 2) how to fix it.

Thanks for any help.


Hi everyone

I hope someone here is able to help me, cuz I haven't succeed in finding the right way myself.

I have made some macros helping me importing data from a txt fileand to sort the data into a sheet ready to be exported into another txt file. I have made VBA helping me to work with "." as decimal separtor within the spreadsheet.

But, when I run the macro to export the data into a txt file (I have used the macros from this site http://www.cpearson.com/excel/ImpText.aspx) it automatically changes all "." into ","

But why?!?

I need the txt file to load data into another program, and this program need the use of "." as decimal separator - and not ",".

I'm using an European windows system...

BR
Lysebjerg78


Can somebody tell me how you insert a vertical separator in a custom defined ribbon? I have several text buttons calling various macros and I want the layout to be along the lines of 3 rows of buttons in a column, then the separator, then the next three buttons, the separator etc, but all within the same group.

Thanks for any help!


Hi again!

I have a problem that i have a load of cells which a large amount of text which also contains some numbers, these numbers have to show the thousand separator, obviously just checking the separator box doesnt work as text is involved as well.

Any ideas?

Many thanks

Raventroth


Hello,

Is there a ready shortcut to set the cell format to "Number" with 0 decimal places (i.e. integer) and a 1000 separator?
I assumed it was in a common use but I couldn't find a pre-defined format for that. Basically I just need to be able to put a button for the #,##0 format on the ribbon or the quick access toolbar.
Thanks.


Hello

When working with large numbers the "thousand separator" doesnt work for the whole number, of example:

Original Number: 2800000000

Expected Result: 2.800.000.000

Result Obtained: 2800000,000.00

I hope the separator could be a dot but a comma works just fine.

I am using Office 2007 and the Crystal Ball add-on

Any ideas on how to make this work?

Thanks a lot for your help


I copied a column of Mainframe data to Excel and any numbers with 6 digits
plus the thousand comma separator will somehow transposed into another widely
different number. All 5 digits and less plus the thousand comma separator
worked fine. Is there a setting that triggers Excel to do this or is it
simply a bug? Thanks!



I've got the following problem.
I create a new workbook (single sheet) processing an original workbook.
The "new" workbook is very simple: 3 columns, a description and two numbers
columns.
I get the problem when I save the new workbook via VBA with the saveas
function as in follows
new_workbook.saveas Path, xlText

The execution produces a file where decimal separator is always "." despite
regional settings.
The strange thing is that if I "save as" the workbook from Excel main menu,
selecting TXT format, the resulting file is correct with decimal separator
that follows regional settings.

I get the same problem with Excel 2000, XP, 2003.
Format of the number cells doesn't affect the result.
Any suggestion?

Regards

Massimo



I don't know if this is even possible but I am trying to come up with a way to add a line separator (or something similar) between years for a chart that is interactive (I have a scroll bar that is linked to the chart and 10 years of monthly data).

The axis labels are formatted as "mmm" and I don't want to change to format "mmm-yy". I am hoping there is a way to add this separator as it is very hard to identify what year a user is looking at as he/she scrolls through the data.

I have attached a sample of spreadsheet that better clarifies how it is currently set up and the desired look.

If this is not possible, any other suggestions are surely welcomed. Thanks


Using schema.ini file, one should be able to define format of txt or csv file for import into Excel. I am using this functionality in some cases, so I am not absolute noobe here, perhaps first grader. However, I struggle to set up schema.ini file in one particular occasion. This is very important to me, as I have lots of txt;csv files coming out of PeopleSoft in this format:

"Instance","ID"
"1661437","NOVAT"
"1661432","NOVAT"
"1661439","NOVAT"
"1661445","NOVAT"

Please note that all fields are wrapped in double quotes and separated by comma which is also decimal separator on my PC. I therefore define in shema.ini file as follows:

[test.txt]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=5
CharacterSet=OEM
DecimalSymbol=.
Col1=Instance CHAR
Col2=ID CHAR

Decimal symbol is defined, not for this particular example, just in other cases where I have numbers, dot is actually decimal separator.

I then try the usual Data Import path:
Data->Import External Data->New Database Query ->

I then give any name to data source, select txt, csv driver and click Connect. On the following screen I select directory and click Options. I then get first error message :

From Temp_Excel


I then try to disregard error message and select the file I want to import and make Excel guess the structure. This steps works on simple and straight forward examples. Not on mine one howewer. I get second error message:

From Temp_Excel

It looks like Excel does not like that fields are separated by comma. But this is unfortunatelly how Peoplesoft exports data and I can not change it. I thought this was the idea of schema.ini file to actually tell Excel how fields are separated, which would overrule default regional settings in control panel.

I managed to get arround be opening txt file and replacing all commas with | and then defining it as delimiter. That works, but kind of strange workaround and also additional step for every file exported...

How can I import data without manual changes I do now to txt file. I just want standad Peoplesoft file with shema.ini as a companion. Is that too much to ask ?


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.


Can you help me to change a time format in cell A1 from H.MM.SS to cell A2 in H:MM:SS please. The difference being a colon instead of a full stop.The formula bar in both cases is showing 0:12:34AM but the next part of the work returns #VALUE! as the result. This is what I need to happen next. The next cell A3 changes A2 into a number like 0.123456 which then has 2% added to it and returned in cell A4. And finally cell A5 returns a new time as H.MM.SS again. I think there are some hidden cels that do this final conversion.
Many thanks
Paul

Hi all, I need to know how to avoid WebQuery to recognize formats on the fly, and just import everything as text.

Because of this "feature" Im loosing data when querying a web page, and must fix it manually...

For example, in the web page there is a number like "500.000" but after query it land as "500" in worksheet ( point "." is thousands separator) so i need the query to get it as text so i dont loose any digits.

Some things Ive tried already and no working:
- Search many forums and google, cannot find working solution.

- Setting cells format to text prior to query. Not working (excel will change format to number anyway)

- Change format to Text after query. Not working. Digits are lost for good, so nothing to do after the query.

- .WebDisableDateRecognition. This works only for dates, not for numbers.

- .PreserveFormatting. Not working .Tried true and false, no difference.

- .WebFormatting. Not working. All combinations, same result.

- Change Windows thousand separator in Control Panel. This might work, but code wont be fully portable to other users using different configuration.

So problem is very simple, i need web query to import EVERYTHING as plain text. Dont need Excel to be smart in this case...


Thanks in advance!!

Nestor


I am trying to create a self-cleaning pivot table that automatically deletes unwanted drill down tables. I have found some code through google; however, I keep getting a Compile Error: Expected: list separator or). Below, is the section that keeps producing the error:


Private Sub Workbook_SheetBeforeDoubleClick( _
ByVal Sh As Object, ByVal Target
As Range, _
Cancel As Boolean)


I currently have a sales forecasting report that I create each month, which contains statistical data for the prior months, as well as forecasting fields for the new months.
When I create a pivot table for the statistical data, the field settings default to COUNT and I need them to all be changed to SUM.
I currently change each one individually but there's just got to be an easier way. Right??

Also, is there a way to change the format of the cells to default to Number, Zero Decimal places with a comma separator per 1,000.

Thanks!!


I am experiencing an issue with number format in excel 2007 small business version. Whenever I store any number with decimal point it consider it as a text, for example I enter 2.50 it stays left aligned and when we enter other numbers and try to sum the numbers it does not give correct result as it does not consider the numbers with decimal point as number but instead considers them as text. If I enter a number without decimal point it works fine.

Moreover even if I change the decimal number format to number it still does not change to number format and it remains left aligned as if it were a text. I have tried to uninstall MS Office and then reinstall it again but still the problem persists. Please advice what could be a possible reason for this issue...

I am attaching a specimen of the file with issue...

Hi

I've a function 'piece' that extracts text from between 2 delimiters in a string. E.g. text = "123-abc-567", piece(text,"-",2)="abc"

It currently goes through character by character looking for the delimiters.

Some time ago, someone mentioned on here that there was a function in XL2007 VBA that did it without the character loop.

I assume it's not worksheetfunction.find; can anyone tell me what it is?

(here's my code)
Code:

Function piece(Searchstring As String, Separator As String, IndexNum As Integer) As String
Dim i, SepCount, SepLen, StartPos, EndPos As Integer
Dim TestStr As String
SepLen = Len(Separator)
SepCount = 0
StartPos = 0
EndPos = 0
For i = 1 To Len(Searchstring) - (SepLen - 1)
    TestStr = Mid(Searchstring, i, SepLen)
    If TestStr = Separator Then
        SepCount = SepCount + 1
        If SepCount = IndexNum - 1 Then StartPos = i + 1
        If SepCount = IndexNum Then EndPos = i
    End If
Next i
If StartPos = 0 And EndPos = 0 Then
    piece = ""
    Exit Function
End If
If StartPos = 0 Then StartPos = 1
If EndPos = 0 Then EndPos = Len(Searchstring) + 1
piece = Mid(Searchstring, StartPos, EndPos - StartPos)
End Function





I have actually saved on my PC the following
CustomDelimiterExport.zip 28.24KB and open the option box..on tools, but when I try to convert my excel worksheet into a text format and I press go it seems not working. I think there's something wrong on the delimiters I am using..
Can you please tell me what I should put into the delimiter box?
I want to have ; instead of , and , instead of .
I know that_ it's a , which is a space in CVS format, but that s what exactly we are trying to replace as we need ; for the file to be read into our system.
Can you please advise?

thanks


I have some code that adds a custom commandbar to Excel, and have been able to add a few buttons to it. How can I add a separator between 2 of them?

sample from my code...

Code:

    Set Mycbar = CommandBars.Add(Name:="EssMenu", Position:=msoBarTop, Temporary:=False)
    
    Set Mycontrol = Mycbar.Controls.Add(msoControlButton)
    With Mycontrol
        .Caption = "Essbase Login"
        .Tag = "Login"
        .OnAction = "Connect"
        .FaceId = 277
    End With
    
    Set Mycontrol = Mycbar.Controls.Add(msoControlButton)
    With Mycontrol
        .Caption = "Retrieve"
        .Tag = "Retrieve"
        .OnAction = "Retrieve"
        .FaceId = 317
    End With





Is there a way to change a date format from DD.MM.YYYY to DD/MM/YYYY in excel without using the Regional and Language Options feature? Thank you.


I have a formula in a cell which is calculating from several cells. When the value comes out at zero, I want a dash to display, not zeros. I have read the tip on this subject, and tried it, but it does not work! I have used the following syntax:

#,##0.00;[Red](#,##0.00);- ;

This should do the following:

1. When the result is positive, the display should have two decimal places with a thousands separator.
2. When the results is negative, the display should have all the above, but be red and have brackets around it.
3. When the result is zero, only a dash should be visible.

When my formula has a zero result, it shows "0.00" or "(0.00)"! Can anybody tell me why??

Jonathan


Whiling finding a solution to my problem, I cam across the following thread. which helped me somewhat but I need a solution to the trailing decimal point also. I need numbering format that allows one decimal place but do not show either zero or the decimal point.

For example , if my number is 9.0, it should be shown as 9 and :" 9." with the decimal hanging . If my number is 9.1, then it should be should as 9.1.

The format of 0.# is not helping me here.


http://www.ozgrid.com/forum/showthread.php?t=129919