Force English formatting

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?
 
J

Justin366E

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.
 
R

Ron de Bruin

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)
 
S

Sandy Mann

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
 
R

Ron de Bruin

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
 
S

Sandy Mann

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
 
R

Ron de Bruin

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting this "French" currency format to "English"? 5
Date Format Problem. 2
How to get the available languages (MUI) of Excel using VBA? 0
Learn English!!!!! Know everything !!!!!!!!!!! 1
Stop excel changing the value of numbers in european formt 7
Decimal Formatting in Windows English vs European Formatting 3
Any suggestions on this one... 4
Access database on a WAN 18

Top