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

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

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

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" wrote in message
...
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

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

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

-----Original Message-----

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" wrote in message
...
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



.

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

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" wrote in message
...
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

-----Original Message-----

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" wrote in message
...
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



.

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

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

-----Original Message-----
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" wrote in message
...
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

-----Original Message-----

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" wrote in message
...
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



.



.

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

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

Sincerelly
Mark

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!