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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,570
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

togo

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,212
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@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

Well-known Member
Joined
Oct 22, 2012
Messages
2,570
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,561
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:="."
 
Solution

togo

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

togo

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,561
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,143,909
Messages
5,721,456
Members
422,363
Latest member
Bogus_Potatoes

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
Top