Urgent - make formula work with both decimal separators

[staeri]

I have a customer who has in my mind a strange request:

In cell A3 I have the formula =A1*A2

The value in A2 is always the same but the user is allowed to enter a
value in A1 and my customer wants to make the calculation in A3 work no
matter if the user enters a value with "." or "," as decimal separator.

As it is now if the user enters a value with the opposit decimal
separator compared to standard in A1 the result in A3 is ########.

How can I solve this?

Regards,

S

 

[Niek Otten]

=IF(ISERROR(FIND(",",A1)),A1,VALUE(SUBSTITUTE(A1,",",".")))

If your standard decimal separaor = ".", otherwise the other way around.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have a customer who has in my mind a strange request:
|
| In cell A3 I have the formula =A1*A2
|
| The value in A2 is always the same but the user is allowed to enter a
| value in A1 and my customer wants to make the calculation in A3 work no
| matter if the user enters a value with "." or "," as decimal separator.
|
| As it is now if the user enters a value with the opposit decimal
| separator compared to standard in A1 the result in A3 is ########.
|
| How can I solve this?
|
| Regards,
|
| S
|