RE: Nobody can answer! Problem with commas and dots

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Poppe

I have done some more experimentation on this and my code works for both
cases of your sample data. In my case, I am doing the opposite from you;
i.e. taking numbers from UK locale (123,456.78) to European locale
(123.456,78) format.

I thought that your original data cells might be formatted as text, rather
than numbers. However, running my code on string format numbers still works.
I used "502,200.55" and "502,200.5" as string literals and they came out as
"502200,55" and "502200,50" respectively. The statement
'Format(ActiveSheet.Cells(8, 5),"0.0")' returns "502200.55" and "502200.50"
(in your system, you would have "," instead of "."). In other words, it has
stripped out the thousands separators, leaving just the decimal point. From
this point on, it is simply string manipulation - so providing you don't go
back to a number between deconstruction and the SQL statement, it should work.

Not sure where to go next on this.

--
Nick


"Poppe" wrote:

Thanks for your reply.

I understand your idea, and i thought something similar. But the problem is
that you can never know where the comma exactly is. There can be different
kinds of values.

With this code:

Value 502.200,55 results into value 502.200.55 (should be 502,200.55)

Value 502.200,5 results into value 502.200.,5 (should be 502,200.5)


The Replace() function can change commas to dots, but situation is the same.
I have for example 2000 rows of excel chart with mixed values, changing the
code for all different values is impossible.

I can use code that forces whole windows system wide regional settings to
change while running the code, but that is a dangerous option and not good
coding.




.

Relevant Pages

  • Re: Splitting and comparing file names
    ... with the general format would really be much preferred... ... sample data would be nice. ... You have a case of premature declaration. ...
    (perl.beginners)
  • Re: hash key not found .. WHY NOT?
    ... filea.csv format: ... Where file1.csv is using BK=AK to lookup A3 and A4. ... Not a word of this makes sense nor matches your sample data. ... Please show REAL sample input and REAL sample output. ...
    (perl.beginners)
  • Size limits on IWMWriter::WriteSample ?
    ... building a profile from the stream information from the codec that mostly ... I'm using the "20kbps, 22Khz, Mono CBR" codec format. ... that is missing the end of the sample data. ...
    (microsoft.public.windowsmedia.sdk)
  • Re: Conditional Format
    ... Assuming the sample data: ... Let's split the data first ... Select "MDY" from the drop menu under "Col data format" ... Click Format button> Patterns tab> Light blue? ...
    (microsoft.public.excel.newusers)