thousand separator conversion from dot to comma

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

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


"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

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
--
with kind regards

Spike


"Kevin B" wrote:

> You can do that with Find/Replace (Ctrl+H).
> --
> Kevin Backmann
>
>
> "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

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
--
with kind regards

Spike


"pinmaster" wrote:

> 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
>
> "Spike" wrote:
>
> > 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
> > --
> > with kind regards
> >
> > Spike
> >
> >
> > "Kevin B" wrote:
> >
> > > You can do that with Find/Replace (Ctrl+H).
> > > --
> > > Kevin Backmann
> > >
> > >
> > > "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

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

"Spike" wrote:

> 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
> --
> with kind regards
>
> Spike
>
>
> "Kevin B" wrote:
>
> > You can do that with Find/Replace (Ctrl+H).
> > --
> > Kevin Backmann
> >
> >
> > "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

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.

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

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

Spike


"Dave Peterson" wrote:

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

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

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
>

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

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
>