different ver Excel - problem with decimal separator (for experts)

[Mark]

I have problem with formating data. I work in non-english
ver. Excel, where decimal separator is "," (no "." as
English).


Currently my each cell in raport cointain data like this
below:
e.g.
1.512,00
403,22
4.649.019,11
data imorted with txt file.

I'd like remowe "." by simple code:
Selection.Replace What:=".", Replacement:="",_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Unfortunatelly VBA code remove "," for cell with 403,22 in
result 40322 (sometime work rightly sick!) and "."
12.555,52 in result 12555,55 (right).

Whats wrong in my code?
Is there any way to pass round this error.
Please help me.
Regards
Mark

 

[A.W.J. Ales]

Mark,

The problem is that the thousands seperator points you want to remove just
are not there in Excel. It just shows it in the cell.
Try : Input a large number in A1 and format the cell such that Excel show
the thousands seperators.
Now run this simple routine to shwo that they actually are NOT part of the
number.
Sub RemoveItNot()
A = Cells(1, 1).Value
MsgBox A
End Sub


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Mark]

Tnx for reply but..
1.354,45 isn't formatted the thousands seperator - as i
wrote data imported from txt file (may be as string beside
my ver Excel setup thousands separator as space).
I'd like only remove "." after that i can count and format
data.
Regards
Mark

 

[A.W.J. Ales]

Mark,

I overlooked the fact that you imported from a textfile,

The problem is now that by importing the larger numbers where a
thousandseperator (point) was taken up you actually imported a text, while
the smaller numbers were actually numbers.
By running your macro you did remove the thousans seperator from the
"text"numbers. For the smaller numbers (actually numbers you removed the
decimal points (although Excel show a , in your setup it actually still is a
point). The following routine does what i think you want to do and in the
same time translates the textnumbers to actual numbers.

Sub RemovePoint()
Dim C As Range
For Each C In Selection
If WorksheetFunction.IsText(C.Value) Then
C.Replace What:=".", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
C.Value = C.Value * 1
End If
Next
End Sub


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Mark]

Mr Ales, you're admirable expert!
It works well!
Sincerelly
mark

 

[Mark Gora]

Mr Ales, you're expert!
It works well!

Sincerelly
Mark