VBA - replace characters in string

Question

I have 1 column, which should contain an amount, but it has incorrect formatting and it is treated as text.

Therefore, there is necessary to replace dot for nothing and then comma for dot. I have code:

Private Sub Correction_of_dot()
    Dim i As String
    Dim k As String
    i = "."
    k = ""
    Columns("P:P").Replace what:=i, replacement:=k, lookat:=xlPart, MatchCase:=False
End Sub

Private Sub Correction_of_comma()
    Dim i As String
    Dim k As String
    i = ","
    k = "."
    Columns("P:P").Replace what:=i, replacement:=k, lookat:=xlPart, MatchCase:=False
End Sub

But it does nothing... no errors, just loading and then nothing has happened. Could you advise me, what I've did wrong or what can I do better, please?

Many thanks!

Answer 1

You could loop through the column and use the Replace function.

Dim i As Long
Dim finalRow As Long

finalRow = SheetX.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 to finalRow

   SheetX.Cells(i, 1).Value = Replace(SheetX.Cells(i, 1).Value, ".", "")
   SheetX.Cells(i, 1).Value = Replace(SheetX.Cells(i, 1).Value, ",", ".")

Next i

Note: I didn't test this - but it should work. Also: Change SheetX to whatever sheet CodeName is appropriate and change the column reference as needed (column 1 in this example)

Answer 2

As per my regional settings, Dot is used as a decimal separator and Comma is used as a thousand separator. Having said that, the following code works for me and produce the expected output.

To test the code, I copied the numbers from the web page and pasted on the sheet, they look like below...

Code:

Sub Test()
With Range("P:P")
    .Replace ".", ""
    .Replace ",", "."
    .NumberFormat = "0.00"
End With
End Sub

Output: