View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave O Dave O is offline
external usenet poster
 
Posts: 427
Default 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