Remove dots from number

user_3540466 0
Viewed 10204

I have a column with numbers formatted as general

| 2.500,00 |
| 70,2     |
| ...      |

I have a macro that tries to delete the dot in the column.

When I do it manually the result is

| 2500,00 |
| 70,2    | 
| ...     |

When I do it with the following line:

Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

it removes the comma in the cells where there is no dot, the result looks like this:

| 2500,00 |
| 702     |
| ...     |
vba excel replace
5 Answers

I had the same problem. Also using comma as decimal separator. When replacing dots manually, I had no problems at all, when I had VBA do it, commas were replaced and data got corrupted. What helped me was that I first used the trim function in excel, then I pasted it as values and only after that I replaced the dots with "nothing".

Here's the code:

    Sub dot_replace()

'first, trim cells
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
    Range("M1").Select
    Selection.AutoFill Destination:=Range("M1:M3")
    Range("M1:M3").Select
'copy and paste as values
    Columns("M:M").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'then proceed with replacing
    Columns("M:M").Select
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

Hope this helpes someone

user_108957380

my solution, using loop and replace function; input: values in the column "D", number format "00.000,00-" for negative numbers (for positive without minus sign); output: numbers in the format ("-00000,00) to be in the column "E";

'i is number of the row
Dim i As Integer
'1 is heading
i = 2
'lastRow in the number of the last row
Do While i <= lastRow
    'store value to be converted as string
    Dim str As String
    'convert from "00.000,00" to "00000,00"
    str = Replace(Range("D" & i).Value, ".", "")
    'myNum: number to be outputed
    'needs to be double, otherwise risk of errors or loss of decimals
    Dim myNum As Double
    myNum = 0

    'check for negative (format "00000,00-"
    If Right(str, 1) = "-" Then
        'remove minus sign
        str = Left(str, Len(str) - 1)
        'convert to double and make negative
        myNum = CDbl(str) * -1
    Else
        'not negative, simply convert do double
        myNum = CDbl(str)
    End If

    'store in the column with the output
    Range("E" & i).Value = myNum
    'continue on the next line
    i = i + 1
Loop
user_88673390

Sheet1.Range("E:E").Replace What:=".", Replacement:="", lookat:=xlWhole, MatchCase:=False

user_91025000
Related