Decimal moving and values lost when converting from string

[JimmyA]

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

[martindwilson]

what's the rest of the code?

[K m]

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

[martindwilson]

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

[JimmyA]

Hi K m

Thanks but I used to have it formatted as text but it now needs to be a value and that is where the problem has started.

Hi martindwilson

This is the rest of the code for the column

Please Login or Register  to view this content.

Thanks

Jim

[JimmyA]

Thanks martindwilson

The value should show

29.442,72

In the Netherlands they have full stops as separators and commas as the decimal.

My Laptop has been set to Dutch settings so I was expecting it to show correctly.

Any ideas?

Thanks again

Jim

[martindwilson]

Please Login or Register  to view this content.

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

[rkey]

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.

[JimmyA]

Boom!! Thank you very much Martin

That looks to have sorted it!!

Thanks again

Jim

[JimmyA]

Hi rkey

Sorry I'm a bit slow on the typing.

Thanks very much for your help, I will take a look at this too.

Cheers

Jim