VBA - decimal separator issue

togo

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Selection.Replace What:=".", Replacement:=","
but after that, I'm getting errors for each cell - number formatted as text.decimal number.
1618292459302.png

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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Welcome to MrExcel Message Board.
Try Both Methods and See what works. Change Rng to your Range.
VBA Code:
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

VBA Code:
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
 
Upvote 0
Welcome to MrExcel Message Board.
Try Both Methods and See what works. Change Rng to your Range.
VBA Code:
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

VBA Code:
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.
 
Upvote 0
@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
 
Upvote 0
Try this also:
VBA Code:
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
 
Upvote 0
Since VBA works as if you have US regional settings, you could try simply replacing the dot with a dot:

Code:
Selection.Replace What:=".", Replacement:="."
 
Upvote 0
Solution
@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.
 
Upvote 0
Try this also:
VBA Code:
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.
 
Upvote 0
Since VBA works as if you have US regional settings, you could try simply replacing the dot with a dot:

Code:
Selection.Replace What:=".", Replacement:="."
As I wrote in my original post, this is not working, since I'm getting numbers formatted as text.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top