I also use an Excel with a French locale, and often face this problem with CSV files. As explained by others, the French numbers use a coma as a decimal separator, and a number like 123.45
is interpreted as a text by Excel.
The fastest way to circumvent this, is to replace .
with ,
. You can do it quickly with Find/Replace, as suggested by David.
For those who face this problem quite often, you can use this piece of code (adapted from this answer), save it in your PERSONAL workbook and assign it to a button in the ribbon:
Sub Comas2Dots()
Application.ScreenUpdating = False
If (MsgBox("Do you want to replace comas by dots?", vbOKCancel) = vbOK) Then
Const sTEMPCOMMA = "|comma|"
Const sTEMPDOT = "|dot|"
Const sCOMMA = ","
Const sDOT = "."
If TypeName(Selection) = "Range" Then
With Selection
.Replace sCOMMA, sTEMPCOMMA, xlPart
.Replace sDOT, sTEMPDOT, xlPart
.Replace sTEMPCOMMA, sDOT, xlPart
.Replace sTEMPDOT, sCOMMA, xlPart
End With
End If
End If
Application.ScreenUpdating = True
End Sub
Hope that helps people experiencing this unnecessary problem!
Additional documentation
- How to assign macros to the ribbon
- Copy your macros to a Personal Macro Workbook