Force English formatting

  • Thread starter Thread starter Justin366E
  • Start date Start date
J

Justin366E

I have an application used by both English and European clients. This
is significant because often when European clients are entering Data
into the TextBoxes, they will accidentally enter it in the format that
they are used to: 1,000.00 in EU formatting = 1.000,00. This of
course screws up my whole application.

How should I go about safe-guarding this from happening?
 
More Info: What I'm looking for is not a way to safe-guard entering
the info in the TxtBox wrong, but more of a way to convert it if it is
sitting in the cell wrong upon Worksheet Open... Some European clients
enter data in Europe where that is the acceptible format, and then when
it's opened in the US, the data opens as 1000,50 instead of 1000.50,
and it crashes my application.
 
Be sure that the cell is not text then Excel will change it when you open it in the US

Here is a formula to convert the textvalue in I2 to US

=IF(ISERR(I2*1),IF(ISERR("1.2"*1),SUBSTITUTE(SUBSTITUTE(I2,",",""),".",",")*1,SUBSTITUTE(SUBSTITUTE(I2,".",""),",",".")*1),I2*1)
 
Ron,

Won't ISERR("1.2"*1) always return FALSE regardless of the entry in I2? At
least it seems to for me. That being the case, may I ask what is its
function in your formula?

--
Puzzled

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Hi Sandy

"1.2"*1 give a error on my Dutch machine because we use 1,2
This way the If statment know what to do

On my Dutch machine the English text 123.45 will be the value 123,45
On my English machine the Dutch text 123,45 will be value 123.45
 
Hi Ron,

Ah Yes! I see now - an international formula - clever

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top