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

c.Select
c.Value = Right(c.Value, Len(c) - 3)
Selection.NumberFormat = "#,##0.00"
c.Value = Val(c)

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)

c.Select
c.Value = Right(c.Value, Len(c) - 3)
Selection.NumberFormat = "#,##0.00"
c.Value = Val(c)

i think its because

c.Value = Right(c.Value, Len(c) - 3)

is text


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

c.Value = Right(c.Value, Len(c) - 3) + 0

gives
29.44272

c.Value = Right(c.Value, Len(c) - 3) + 0

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.

Sub test()
    For Each c In Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(lastrow + 2, 0)).Cells
        'Strip all dots
            c.Value = Replace(c.Value, ".", "")
        'determine number of decimals in original
            IntDecimals = Len(c.Value) - InStr(c.Value, ",")
        'Strip the rest of non-numeric values and comma's
            b$ = ""
            For i = 1 To Len(c.Value)
                a$ = Mid(c.Value, i, 1)
                If a$ Like "[0-9]" Then
                    b$ = b$ & a$
                End If
            Next i
            c.Value = b$
        'Devide by number of decimals
            c.Value = c.Value / (10 ^ IntDecimals)
        'Format Cells
            c.NumberFormat = "#,##0.00"
    Next c
End Sub