alejandrofh2

Hi,

I am downloading certain information from SAP and try to do some calculations with it, but somehow I am not able to change the number format with the macro i recorded. My decimal separator in excel is , and thousands separator is blank

Below examples on how numbers come from SAP:
-1.443,65
-733,89
-618,97
-1.668,06
-8.141,00

And I recorded a macro to convert them, ie: remove the dot and leave blank

.Columns("G:I").Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

but when executing the macro, the results are wrong

-1443,65
-73389
-61897
-1668,06
-8141,00

It changed from 733,89 to 73389 and so on. I dont understand why the , was changed if the macro was supposed to change the dots

Could you please help me to fix this issue? Thanks in advance for your help

Br, Alejandro

Josh Hazel

I tried it on my PC and it did not change the , to blanks... You might have your PC setup with "," being listed as a decimal, so when you find/replace for decimal perhaps the pc also replaces the commas.

See if this helps:

By default, Microsoft Excel uses the system separators that are defined in the regional settings in Control Panel. If you sometimes need to display numbers with different separators for thousands or decimals, you can temporarily replace the system separators with custom separators.

On the Tools menu, click Options.
On the International tab, under Number Handling, 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 the Use system separators check box.