Excel adjusts decimal separators automatically (as other language-dependent aspects) when opening a sheet in another country. This also works inside TEXT(A1,"#,##0.00"). But how can I SUBSTITUTE() them?

Name Insert Define a cell or the following formula as K:

K: =MID(0.1,2,1) returns ".". Using the sheet in Germany, it returns ",".

(Note: Pocket Excel requires a cell name because it does not support named formulas).

Now this definition can be used:

Imagine you want a number to be rounded by quarter. After that, imagine that .25 always has to be changed to .5. The native rounds .5 and .75 and .0 remain unchanged.

The formula for this is

=--SUBSTITUTE(ROUND(A1*4,0)/4,K&2,K)

Why that? The hard-coded formula

=--SUBSTITUTE(ROUND(A1*4,0)/4,".2",".")

would only work with the point used in the U.S. as decimal fraction. It will not change the German comma.

The professional solution:

Use the Excel4 macro function =INDEX(GET.WORKSPACE(37),3) instead. It adjusts the decimal separator for each environment, not only the mentioned two regions. See documentation for =GET.WORKSPACE(37) here. Note: These XL4-functions can only be used within a named formula.