Number format

[Mustakrakish]

Hi everybody,

I have problem with changing number format.
When I export data from SAP, number format is like this: 10.548,45
In excel, this is stored as a text, cause of that dot as a thousands divider. Usualy i do just replace dot with nothing and it works. but when i do it as a macro, for some reason, it replaces comma as well, therefore instead of having number 10 548,45 I end up with 1 054 845.

This is my code for replacing dots.

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

Cell format is number.

Thank you very much

 

[Mustakrakish]

Eh, i should have wrote some question right?

So is there a macro that will take out those dots and converts text numbers to normal numbers?

Thanks :P

 

[jasonb75]

Which version of excel are you using?

This shows how to do what you want, it works in 2003, but not in 2007 (no reference to 2010 so don't know what will happen there).

http://www.howtogeek.com/howto/microsoft-office/import-text-into-excel-2007/

You could use something like

Sub Convert_Decimal()
With Columns("D:F")
    .Replace What:=",", Replacement:="|", LookAt:=xlPart
    .Replace What:=".", Replacement:="", LookAt:=xlPart
    .Replace What:="|", Replacement:=".", LookAt:=xlPart
End With
End Sub

Which should be used with caution! Running it twice will cause the same problem as your recorded code.

 

[gaj104]

This seems to work with me

With Range("D:F")
    .Replace ".", ""
    .Replace ",", "."
End With

UK regional settings and excel 2007

 

[jasonb75]

This seems to work with me

With Range("D:F")
    .Replace ".", ""
    .Replace ",", "."
End With

UK regional settings and excel 2007

Graeme,

I could be wrong but think that the OP's regional settings, or cell formats might be playing a part in the problem

The first line of your code does the same as their recorded code, which they said removed the comma as well, which, thoeretically, would leave nothing for the second line to replace.

Would be so much easier if excel had a simple format conversion tool for region specific values.

 

[Mustakrakish]

I am using 2007 and 2010.

Source file is not text doc, it is normal .xls (2007) file from SAP export.

Thx guys I tried to use your solutions, but somehow comma is still replaced by nothing therefore my numbers are completley messed up

I am confused with the difference between using replace in excel and using it as a macro. I have this problem only when I use it as a macro