VBA - decimal separator issue

[togo]

Hi, I have a data set in the html page looking as table containing numbers, dates, times, strings etc. each in separate column. I'm trying to copy this data set to excel and format it as I want with VBA. The only problem I have is that the source data set is using DOT "." as decimal separator and I want to use COMMA "," instead - according to my regional settings of Windows, excel is not considering DOT separated numbers as numbers.

I can do:

Selection.Replace What:=".", Replacement:=","

but after that, I'm getting errors for each cell - number formatted as text.decimal number.

How can I format it as number by VBA? When I replace DOT with COMMA by GUI Find and replace function it is formatted correctly as COMMA separated decimal number.

 

[maabadi]

Welcome to MrExcel Message Board.
Try Both Methods and See what works. Change Rng to your Range.

Sub Macro1()
Dim i As Long, Rng As Range, Cell As Range
Dim K As Long
Set Rng = Range("A1:A5")

For Each Cell In Rng
K = InStr(Cell.Value, ".") - 1
If K > 0 Then
Cell.Value = Left(Cell.Value, K) & "," & Right(Cell.Value, Len(Cell.Value) - K - 1)
Cell.Value = Cell.Value * 1
End If
Next Cell

Sub Macro1()
Dim i As Long, Rng As Range, Cell As Range
Dim K As Long
Set Rng = Range("A1:A5")

For Each Cell In Rng
K = InStr(Cell.Value, ".") - 1
If K > 0 Then
Cell.Value = Left(Cell.Value, K) & "." & Right(Cell.Value, Len(Cell.Value) - K - 1)
Cell.Value = Cell.Value * 1
End If
Next Cell

 

[togo]

Welcome to MrExcel Message Board.
Try Both Methods and See what works. Change Rng to your Range.

Sub Macro1()
Dim i As Long, Rng As Range, Cell As Range
Dim K As Long
Set Rng = Range("A1:A5")

For Each Cell In Rng
K = InStr(Cell.Value, ".") - 1
If K > 0 Then
Cell.Value = Left(Cell.Value, K) & "," & Right(Cell.Value, Len(Cell.Value) - K - 1)
Cell.Value = Cell.Value * 1
End If
Next Cell

Sub Macro1()
Dim i As Long, Rng As Range, Cell As Range
Dim K As Long
Set Rng = Range("A1:A5")

For Each Cell In Rng
K = InStr(Cell.Value, ".") - 1
If K > 0 Then
Cell.Value = Left(Cell.Value, K) & "." & Right(Cell.Value, Len(Cell.Value) - K - 1)
Cell.Value = Cell.Value * 1
End If
Next Cell

Hi, thank you for the reply. Your code works, but somehow weird. For small numbers like 1.5 it is correct, but for example 20.17945 it converts to 2 017 945, not to 20,17945.

 

[MARK858]

@togo with a copy of your data after you have done the replace can you see what happens if you manually select the column (assuming your data starts in row 1 else select all the data) then.
Click Data
Click Text To Columns
Make sure it is on Delimited
Click Next
Clear all the boxes
Click Finish

 

[maabadi]

Try this also:

Sub Macro1()
Dim i As Long, Rng As Range, Cell As Range
Dim K As Long
Set Rng = Range("A1:A5")
With Application
        .DecimalSeparator = "," 
       .ThousandsSeparator = ""
       .UseSystemSeparators = False
End With
For Each Cell In Rng
K = InStr(Cell.Value, ".") - 1
If K > 0 Then
Cell.Value = Left(Cell.Value, K) & "," & Right(Cell.Value, Len(Cell.Value) - K - 1)
Cell.Value = Cell.Value * 1
End If
Next Cell
End Sub

 

[RoryA]

Since VBA works as if you have US regional settings, you could try simply replacing the dot with a dot:

Selection.Replace What:=".", Replacement:="."

 

[togo]

@togo with a copy of your data after you have done the replace can you see what happens if you manually select the column (assuming your data starts in row 1 else select all the data) then.
Click Data
Click Text To Columns
Make sure it is on Delimited
Click Next
Clear all the boxes
Click Finish

Hi, my data when I paste it into excel already are in the columns. But when I do Text to columns (as you are suggesting) for column with wrong formatted numbers it does nothing.

 

[togo]

Try this also:

Sub Macro1()
Dim i As Long, Rng As Range, Cell As Range
Dim K As Long
Set Rng = Range("A1:A5")
With Application
        .DecimalSeparator = ","
       .ThousandsSeparator = ""
       .UseSystemSeparators = False
End With
For Each Cell In Rng
K = InStr(Cell.Value, ".") - 1
If K > 0 Then
Cell.Value = Left(Cell.Value, K) & "," & Right(Cell.Value, Len(Cell.Value) - K - 1)
Cell.Value = Cell.Value * 1
End If
Next Cell
End Sub

I have tried it, but the result is the same as before.

 

[togo]

Since VBA works as if you have US regional settings, you could try simply replacing the dot with a dot:

Selection.Replace What:=".", Replacement:="."

As I wrote in my original post, this is not working, since I'm getting numbers formatted as text.

 

[RoryA]

The code I posted is not the same as the one you posted. Note that the replacement in my code is also a dot, not a comma.