thousand separator conversion from dot to comma

[Guest]

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

 

[Guest]

You can do that with Find/Replace (Ctrl+H).

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[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.

 

[Guest]

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

 

[Dave Peterson]

It worked ok for me.

What was in the cell beforehand?

 

[Guest]

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

 

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

 

[Guest]

Definitely helpfull, many thanks far neater than my code. Works a dream,
many thanks.