Decimal moving and values lost when converting from string

Hi All

This is driving me mad, I'm hoping I someone can help.

I have written a script that re-formats a data set; part of it takes a text string, removes the leading characters and then displays the remaining numbers. I had just left it as text but it now needs to be shown as a value with 2 decimal places.........this is where the problems started.......

When I convert to a value it drops the decimal values.

I am in the UK on a UK machine but the script is for a user in the Netherlands. I have changed the location settings on my machine and am using Dutch formatted data to test.

I have put an example of what is happening is below,

Starting Value: AUD29.442,72

After this script: (where c is each cell in a given range) (I have selected each cell as it seemed to be the only way to get it to show as a number.)

Please Login or Register  to view this content.

Resulting Value: 29,442

Just wondering if anyone can either spot something I have done wrong or suggest a resolution.

Thanks in advance

Cheers

Jim

what's the rest of the code?

You could try this, if you don't mind the result as text
=RIGHT(A1,LEN(A1)-3)

Please Login or Register  to view this content.

i think its because

Please Login or Register  to view this content.

is text


however
AUD29.442,72
is supposed to resolve to what?

Please Login or Register  to view this content.

gives
29.44272

Please Login or Register  to view this content.

should work then
i changed my regional settings and i get
29.442,72

Hi Jimmy,

I read your post and tried some other approach :D
Works for me and my dutch excel.
Code is also capable of working with more then 2 decimals as the source, and more or less chars at the start.

Please Login or Register  to view this content.