Remove dots from number

Question

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

Answer 1

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,".",""),",","."))