Search code examples
excelvbatrim

Excel VBA .Trim function changes . to ,

Earlier today I was cleaning up data, using the .trim() vba function. Though it caused . to be changed to , and I have no idea why. 

Cells(rw, j).Value = Trim(Cells(rw, j).Value)

Is all I did, and it changed values as follows:

5.2 -> 5,2

One might think that is due to the local settings, though the same column contains values like 3(6.1) which are not messed up.

Thanks in advance

Solution

  • Go to File - Options - Advanced and look for Use system separators. You want the decimal separator to be . and the Thousands separator to be ,

    You can also change these in VBA using the following code:

     Application.DecimalSeparator = "." 
 Application.ThousandsSeparator = "," 
 Application.UseSystemSeparators = False

    As you don't want to changes these setting, change your code to:

    Cells(rw, j).Value = "'" & Trim(Cells(rw, j).Text)

    .Text will give you the string version of what is in the cell rather than attempt to convert it to a number (if it looks like a number).