Definitely helpfull, many thanks far neater than my code. Works a dream,
many thanks.
--
with kind regards

Spike


"Dave Peterson" wrote:

Ah, you have a dot as the thousands separator.

I think I'd use a macro.

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim mySplit As Variant
Dim myStr As String
Dim iCtr As Long

Set myRng = Selection

For Each myCell In myRng.Cells
myStr = myCell.Text
If InStr(1, myStr, ".", vbTextCompare) = 0 Then
'do nothing
Else
mySplit = Split97(myStr, ".")
mySplit(UBound(mySplit)) _
= Left(mySplit(UBound(mySplit)) & String(3, "0"), 3)
myStr = ""
For iCtr = LBound(mySplit) To UBound(mySplit)
myStr = myStr & mySplit(iCtr)
Next iCtr
End If
myCell.NumberFormat = "General"
myCell.Value = myStr
Next myCell

End Sub
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Select your cells to fix and run the macro.







Spike wrote:

I do not understand that as i have just tried it on a laptop Excel 2000 and
put 1.222.333 in cell A1 and 1000 in cell B1 and as you say copy B1 and paste
special multiply A1 and it does not change the dots. If it is less than a
million it works which is very handy.

Would be nice to get this to work as coding a bit messy!!
--
with kind regards

Spike

"Dave Peterson" wrote:

It worked ok for me.

What was in the cell beforehand?

Spike wrote:

Does anyone know an easy way of converting numbers with thousands separated
by dots to commas. One can run a replace macro which is fine but i have some
numbers such as 123.45 which actually means 123,450.00!! and it does not work
with this properly.

I know i could write some code say using instr but is there a simpler way?

Grateful for any advices, probably not using my brain and a basic answer!!
--
with kind regards

Spike

--

Dave Peterson


--

Dave Peterson