CODEWITHSUNDEEP
excelvbareplace
user3540466
user3540466

Reputation: 303

Remove dots from number

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     |
| ...     |

Upvotes: 3

Views: 11762

Answers (5)

00989021570161
00989021570161

Reputation: 1

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

Upvotes: 0

Potocpe1
Potocpe1

Reputation: 35

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

Upvotes: 0

Jakub
Jakub

Reputation: 11

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

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17627

Use the decimal separator defined by the application instead:

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

Upvotes: 2

D_Bester
D_Bester

Reputation: 5891

You need to change the decimal separator used in Excel to comma instead of dot to match your data.

Copied from Microsoft: Change the character used to separate thousands or decimals

Click the Microsoft Office Button Office button image, and then click Excel Options.

On the Advanced tab, under Editing options, clear the Use system separators check box.

Type new separators in the Decimal separator and Thousands separator boxes.

TIP: When you want to use the system separators again, select the Use system separators check box.

NOTE: For a permanent solution, you must change the regional settings in Control Panel.

ANOTHER SOLUTION:

Actually what I usually do is just use a formula to remove dots and convert comma to dot. Then use formatting to show the number however you want.

=VALUE(SUBSTITUTE(SUBSTITUTE(A1,".",""),",","."))

Upvotes: 2

Related Questions