Number format

Mustakrakish

New Member
Joined
Nov 7, 2011
Messages
5
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

New Member
Joined
Nov 7, 2011
Messages
5
Eh, i should have wrote some question right? :biggrin:

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

Thanks :P
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
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

Code:
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.
 
Last edited:

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
This seems to work with me

Code:
With Range("D:F")
    .Replace ".", ""
    .Replace ",", "."
End With
UK regional settings and excel 2007
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
This seems to work with me

Code:
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 :confused:

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

Mustakrakish

New Member
Joined
Nov 7, 2011
Messages
5
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 :(
 

Forum statistics

Threads
1,081,863
Messages
5,361,743
Members
400,654
Latest member
Pinaki Chatterjee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top