Set local number format from Dutch format Number

**JimmyA** · 11-23-2012, 04:52 AM

Hi All

I have written a script that takes a string and removes the 3 characters at the front. After the 3 characters have been removed I would like to display the remaining as a value with 2 decimal places.

The only problem is that string shows the number in the Dutch format e.g. EUR5.000,00

Is it possible to format the remaining as a number to 2 decimal places acording to the machine it was run on? e.g. if it was run in the UK it would show 5,000.00 and if it was run on the Netherlands it would show 5.000,00.

Also if this was run in the Netherlands does anyone know if this example would automatically convert it to a number?

I hope someone can help!

Thanks in advance

Jim

**RHCPgergo** · 11-23-2012, 04:58 AM

Hi,

I have some experience with this, it gave me a lot of hassle some months ago with Hungarian vs American date format...

I think if excel once recognizes something as a certain data type, then it will be able to display it correctly with whatever different language settings (excel uses language settings that are set in windows control panel).

So... in my opinion if you use your script AND excel recognizes that it's a currency format, it should work in all countries. You can test that by changing your language settings.

Edit: you can easily see if excel sees something as a number: it is aligned right. Text is aligned left by default.

**JimmyA** · 11-23-2012, 05:44 AM

Hi RHCPgergo

Thanks very much for you quick response. It is currently leaving it as text but I wonder if that is because I am running it on a UK machine.

I will see if I can test it by changing the language settings.

In the meantime if anyone has any script ideas then that would be great.

Thanks again

Jim

**RHCPgergo** · 11-23-2012, 06:30 AM

How about this one, it worked for me.

Sub ReplaceFormat()
'assuming the values are in column A, starting from row 2
'what it does: change EUR5.000,00 format to 5,000.00 format.
Dim Lr As Integer
Dim i As Integer
Dim Str As String

Lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lr
    Str = ActiveSheet.Range("A" & i).Value
    Str = Replace(Right(Str, Len(Str) - 3), ".", ",") 'remove "EUR" and replace all "." with ","
    ActiveSheet.Range("A" & i).Value = Left(Str, Len(Str) - 3) & "." & Right(Str, 2) 'replace last "," with "."
Next i
End Sub