Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
International decimal number formats
You will be aware that some international number formats use ',' (commas) to
denote decimal places, whilst others use '.' (points). I have tried to write a macro that will write a decimal number into a cell, and have tried to define the number format by making use of lines: Range("$A$1"),numberFormat = "0.0000" OR Range("$A$1").Value = Format(number,"0.0000") Neither of these lines work. I need to find a VBA command that will define the position of the decimal place, regardless of whether the number format used by the user uses a '.' (point) or a ',' (comma) to define the position of the decimal place. Does anyone know how I can achieve this? Regards J -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200701/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
International decimal number formats
Hi, John-
The information contained in a cell and the way that information is diplayed are frequently different, and driven by the cell's format and Windows Regional and Language Settings. For instance, if you enter an Excel-recognizable date in a cell (try your local equivalent of 10 January 2007) then reformat that cell to a numeric format, the cell will display 39092. We should consider that as we develop a solution to your question. That said, I'm honestly not sure what your question is. When you say "define the position of the decimal place", do you want to determine significant digits? Or convert between formats? Dave O |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
International decimal number formats
Hi Dave,
I want to be able to convert between formats. For example, in certain international formats the user might enter "23,39" to denote the US decimal number "23.39". When I try to format the storage cell in my application with the VBA code 'Format(number,"0.0000")' Excel stores the number as a whole number, 23 if I recall correctly. One thing is certain the number is not stored such that it is recognisable as "23.39" in US or UK number formats. Can you help? Regards J Dave O wrote: Hi, John- The information contained in a cell and the way that information is diplayed are frequently different, and driven by the cell's format and Windows Regional and Language Settings. For instance, if you enter an Excel-recognizable date in a cell (try your local equivalent of 10 January 2007) then reformat that cell to a numeric format, the cell will display 39092. We should consider that as we develop a solution to your question. That said, I'm honestly not sure what your question is. When you say "define the position of the decimal place", do you want to determine significant digits? Or convert between formats? Dave O -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200701/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
International decimal number formats
John-
I typed some decimal numbers and they appeared on-screen with my default settings: a period as decimal. Then I clicked ~Start ~Control Panel Regional and Language Options and clicked the Custom button. This allowed me to show my decimal places with a comma; the numbers I had already entered were converted automatically. So it sounds like no conversion is required at all: if you're exchanging spreadsheets with places that use a comma to denote the decimal place, they will likely have their preferences set in Control Panel. No additional effort would be required. I also tried as many ideas as I could to perform your desired conversion, and did in fact generate a way to do it. However, it would include entering numbers in a particular column and then hiding that column; somewhere in the workbook you could have a "user preferences" section where the user enters a P for period and a C for comma. Then in a helper column right next to the hidden column enter a formula, perhaps something like this: =IF(A1="C",INT(C11)&","&TEXT(INT(MOD(C11,1)*100)," 00"),C11) ....where A1 is the P or C column, and C11 is a hidden numeric entry column. The problem with this idea, or should I say "among the handful of problems" for there are many: this creates a text entry, so you cannot perform math on it, and it's just generally chunky and ugly and inelegant. What do you think about the Regional and Language Options idea? And just for curiosity, what is your timezone? I am on the East Coast of the USA, GMT -5 Dave O |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
International decimal number formats
Thanks Dave,
I agree there should be a simple solution to this. If I describe in a little more detail what I am trying to do it may help. I'm designing an application based on Excel for use in the company I work for that has offices worldwide. Part of the function of the application involves entering decimal numbers in a userform then saving these decimal numbers in spreadsheet cells, so that they can be retrieving at a later date. The problem I was having was that when the application stored the decimal numbers in decimal formats using ",", it wasn't storing and retrieving the numbers properly. I tried formatting the cells using Format(number,"0.0000"), which didn't work when the regional format used a ",". It works fine when the regional format uses a ".". I also tried formatting the cells using Format(number,"0,0000") for regional formats using a "," (i.e. by checking Application.International (xlDecimalSeparator), which also didn't work. I'll try out your suggestions and I'll try a few additional options that I've thought of such as checking what is recorded if I were format a cell in a regional format that uses a "," by recording the code when I format the cell manually. I find regional options a pain to deal with. My timezone is GMT. I'm based in the U.K. Regards J Dave O wrote: John- I typed some decimal numbers and they appeared on-screen with my default settings: a period as decimal. Then I clicked ~Start ~Control Panel Regional and Language Options and clicked the Custom button. This allowed me to show my decimal places with a comma; the numbers I had already entered were converted automatically. So it sounds like no conversion is required at all: if you're exchanging spreadsheets with places that use a comma to denote the decimal place, they will likely have their preferences set in Control Panel. No additional effort would be required. I also tried as many ideas as I could to perform your desired conversion, and did in fact generate a way to do it. However, it would include entering numbers in a particular column and then hiding that column; somewhere in the workbook you could have a "user preferences" section where the user enters a P for period and a C for comma. Then in a helper column right next to the hidden column enter a formula, perhaps something like this: =IF(A1="C",INT(C11)&","&TEXT(INT(MOD(C11,1)*100), "00"),C11) ...where A1 is the P or C column, and C11 is a hidden numeric entry column. The problem with this idea, or should I say "among the handful of problems" for there are many: this creates a text entry, so you cannot perform math on it, and it's just generally chunky and ugly and inelegant. What do you think about the Regional and Language Options idea? And just for curiosity, what is your timezone? I am on the East Coast of the USA, GMT -5 Dave O -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200701/1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
International decimal number formats
Good morning, J-
My apologies for not responding sooner: I access newsgroups using Google groups, and they must have changed the way they store or present data: I couldn't locate this post (despite a fairly arduous search). But now apparently they've changed back to the earlier format, so I was able to find it. Here's an idea that may work: some additional code that prompts the user to enter his preferred decimal format, and stores that in a Named Range in the workbook. Then allow the user to enter numbers as usual, but provide some data validation within the code that checks to see if he has entered a period or comma, and then makes the entry using the preferred decimal format- irrespective of whether he has entered a comma or a period in the input box. This method may require you to change the data type of the previously declared variable that handles the input, but seems workable. Could that work for you? Dave O |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel question Formats and number pad | Excel Discussion (Misc queries) | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
International number separator | Excel Worksheet Functions | |||
number formats default to 2 decimal points when entered so 24 bec. | Excel Discussion (Misc queries) |