thousand separator conversion from dot to comma

S

Spike

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!!
 
S

Spike

Yes you would have thought so, it was the first thing i tried, this is odd
data i think ex a text file and i have run "convert to text' over it several
times and cannot make any headway. so think i will have to write a function
using instr
 
S

Spike

Thanks, i have already tried that and it does not work, maybe something other
than ex text file i will go ahead and code it.

Thanks for your advice
 
P

pinmaster

You can use Text to columns under Data, start by selecting your data, then go
to Data/Text to Columns, select "Fixed width" hit Next Next then hit Advance,
in the the "thousands seperator" menu select the period (.) hit OK Finish.

HTH
JG
 
D

Dave Peterson

One more way.
Put 1000 in an empty cell
edit|copy
select your range to fix
edit|paste special|check multiply

clean up that cell with 1000 in it.
 
S

Spike

Yes that works fine with any thing up to a million, over that it stays as dot
separated
 
S

Spike

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!!
 
D

Dave Peterson

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.






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!!
Click to expand...
 
Top