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!

When and How are those Subs been called? Isn't it easier just changing the column datatype to the format needed? — Quirinux
– Quirinux, Commented Oct 10, 2017 at 9:28
The code appears correct however it may be worth explicitly declaring the worksheet e.g Worksheets("Sheet1").Columns("P")... — Leroy
– Leroy, Commented Oct 10, 2017 at 9:39

JimT · Accepted Answer · 2017-10-10 13:49:08Z

4

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)

answered Oct 10, 2017 at 13:49

JimT

1691 silver badge15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Subodh Tiwari sktneer · Accepted Answer · 2017-10-10 11:34:29Z

1

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:

answered Oct 10, 2017 at 11:34

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Comments

Tom · Accepted Answer · 2017-10-10 10:02:00Z

Try using this. The code first changes your language to match what the decimal separators are in your column. Which then makes Excel recognise it as a number stored as text. It then uses text to columns to convert the range to numbers before resetting the decimal (and thousands) separators back to what they should be - leaving your data now stored as numbers

Sub CorrectIncorrectNumberFormat()
    With Application
        .UseSystemSeparators = False
        .DecimalSeparator = ","
        .ThousandsSeparator = "."
    End With

    'Update to your applicable range I've set it to sheet1 Column A
    With Sheet1
        With Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1))
            .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True
        End With
    End With

    With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = ","
        .UseSystemSeparators = True
    End With
End Sub

Kresimir L. · Accepted Answer · 2017-10-10 10:10:52Z

1

Easy solution that does not require Macro intervention is to select column with text values, goto Data Tab==>Text to Columns==>Next==>Next==>select General Column data format.

You can also tell Excel if comma is a decimal or thousand separator (under advanced options)...

edited Oct 10, 2017 at 10:10

answered Oct 10, 2017 at 9:41

Kresimir L.

2,4412 gold badges12 silver badges22 bronze badges

4 Comments

Tom Over a year ago

This doesn't work in this case due to the mix of decimal separators

Kresimir L. Over a year ago

Actually it works, in case it doesn't recognize automatically, you can always use advanced Text settings...

Tom Over a year ago

Didn't work in my test but didn't try to use the Advanced Text Import Settings which I see you've now added

Patrick Honorez Over a year ago

for large datasets, this is clearly the most efficient solution, including from VBA

Max · Accepted Answer · 2017-10-10 09:50:30Z

0

your code works fine for me; if it doesn't work in your case I think the column is formatted as text; just change the formatting to numbers or Standard.

try this:

Private Sub Correction_of_dot()
    Columns("P:P").Select
    Selection.NumberFormat = "General"

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

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

edited Oct 10, 2017 at 9:50

answered Oct 10, 2017 at 9:37

Max

7591 gold badge7 silver badges20 bronze badges

Comments

Vityata · Accepted Answer · 2017-10-10 10:06:11Z

This is a way to do it:

Public Sub TestMe()

    Debug.Print generateNumber("1.525,32")
    'works with mixed formats:
    Debug.Print generateNumber("12,525.33")
    Debug.Print generateNumber("1,112.525,35")

End Sub

Public Function generateNumber(strInput As String) As Double

    strInput = Replace(Replace(strInput, ".", ""), ",", "")

    generateNumber = CDbl(Left(strInput, Len(strInput) - 2))

    'getting the first digit after the comma
    generateNumber = generateNumber + 0.1 * CLng(Mid(strInput, Len(strInput) - 1, 1))

    'getting the second digit after the comma
    generateNumber = generateNumber + 0.01 * CLng(Mid(strInput, Len(strInput), 1))

End Function

If the input is standard, with 2 digits after the whole number, it would return a standard output.

It has a small plus over formatting, because in different countries the separators are different.

Collectives™ on Stack Overflow

VBA - replace characters in string

6 Answers 6

Comments

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related