Different computer settings

[pruleone]

Hello,

Different countries use different computer settings to seperate decimal points.
Some country will use comma, some country point.

Is there any possibility to force excel to understand what kind of setting user is using?

I mean that I would like to insert to cell formula that if system is like this, then use comma, but when this, then use point.

 

[JockMullin]

In fact this should be automatic. Based on the locale settings in the computer opening the sheet, dates and dollars should be so displayed. Internally they are stored in a standard format, it is only the display of the data which changes based on the locale settings.

So you shouldn't have to do anything.

Jock

 

[pruleone]

I wrote formula (something like this) - =text(c4;# ###,##)

Problem is that if in one country I use comma to separate decimal point, then this formula works well. But if same excel will be used in country where by default is used point then this formula do not work ok.

For example if c4 = 12456.00, then you will see 12 4,56.
So it will show totaly wrong figure.

My idea was that maybe there is somekind of possibility to make excel change this comme from formula to point or just I will write formula that if country is like this then use text (c4;# ###,##) if no then use text (c4; # ###.##).

I can also just remove those decimal points from formula but it would be nice to show those thing too
So if there is somekind of solution then I would like to know it

 

[mintjulep]

With VBA you can access

application.DecimalSeparator
and
application.ThousandsSeparator

 

[combo]

Alternatively, you can use excel 4 macro function:
- insert name appExcel, with refers to =GET.WORKSPACE(37)
- use in worksheet =INDEX(appExcel, Number), where number is one of those below (some may be obsolete).
You can use whole formula in name too (=INDEX(GET.WORKSPACE(37),Number). Please note that since excel 2003 there is masro warning due to macro language in formula.

These values apply to country codes:

1	Number corresponding to the country version of Microsoft Excel.
2	Number corresponding to the current country setting in the 
	Microsoft Windows Control Panel or the country number as 
	determined by your Apple system software

These values apply to number separators:

3	Decimal separator
4	Zero (or 1000) separator
5	List separator

These values apply to R1C1-style references:

6	Row character
7	Column character
8	Lowercase row character
9	Lowercase column character
10	Character used instead of the left bracket ([)
11	Character used instead of the right bracket (])

These values apply to array characters:

12	Character used instead of the left bracket ({)
13	Character used instead of the right bracket (})
14	Column separator
15	Row separator
16	Alternate array item separator to use if the current array separator is the same as the decimal separator

These values apply to format code symbols:

17	Date separator
18	Time separator
19	Year symbol
20	Month symbol
21	Day symbol
22	Hour symbol
23	Minute symbol
24	Second symbol
25	Currency symbol
26	"General" symbol

These values apply to format codes:

27	Number of decimal digits to use in currency formats
28	Number indicating the current format for negative currencies
	0 = ($currency) or (currency$)
	1 = -$currency or -currency$
	2 = $-currency or currency-$
	3 = $currency- or currency$-
	where currency is any number and the $ represents the current currency symbol.
	29	Number of decimal digits to use in noncurrency number formats
30	Number of characters to use in month names
31	Number of characters to use in weekday names
32	Number indicating the date order
	0 = Month-Day-Year
	1 = Day-Month-Year
	2 = Year-Month-Day

These values apply to logical format values:

33	TRUE if using 24-hour time; FALSE if using 12-hour time.
34	TRUE if not displaying functions in English; otherwise, returns FALSE.
35	TRUE if using the metric system; FALSE if using the English measurement system.
36	TRUE if a space is added before the currency symbol; otherwise, returns FALSE.
37	TRUE if currency symbol precedes currency values; FALSE if it follows currency values.
38	TRUE if using minus sign for negative numbers; FALSE if using parentheses.
39	TRUE if trailing zeros are displayed for zero currency values; otherwise, returns 	
FALSE.
40	TRUE if leading zeros are displayed for zero currency values; otherwise, returns 	
FALSE.
41	TRUE if leading zero is displayed in months (when  months are displayed as numbers); otherwise, returns FALSE.
42	TRUE if leading zero is shown in days (when days are displayed as numbers); otherwise, returns FALSE.
43	TRUE if using four-digit years; FALSE if using two-digit years.
44	TRUE if date order is month-day-year when displaying dates in long form; FALSE if date order is day-month-year.
45	TRUE if leading zero is shown in the time; otherwise, returns FALSE.

combo

 

[pruleone]

Thank you,

I will try this VBA based solution.