Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
thousand separator conversion from dot to comma
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comma format button decimals | Excel Discussion (Misc queries) | |||
Importing a part of a big comma delimited csv-file | Excel Worksheet Functions | |||
Comma is not visible in Excel | Excel Discussion (Misc queries) | |||
Comma delimited bank info | Excel Discussion (Misc queries) | |||
HELP - I need to change space delimited to comma? | Excel Discussion (Misc queries) |