Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find...insert
Hemu, try this:
Sub thousands() Dim rng As Range, c As Range Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Worksheets("Sheet1").Range("A1:A" & LRow) For Each c In rng If InStr(1, c.Value, ".") = 0 Then c.Value = c.Value / 1000 c.NumberFormat = "0.000" End If Next End Sub Mike F "Hemant_india" wrote in message ... thanx nigel i want to put ur code in a loop and want to check whether "." already exist in string how do i do that actually i'm importing a sequentil thru import wizard...with fixed width -- hemu "Nigel" wrote: Inserting an additional character into a string can be achieved by splitting the string into two and re-combine with the additional(s) required in its simplest form....... stOriginal = "123000" stResult = Left(stOriginal,3) & "." & Right(stOriginal,3) if it will be numerical then you could just divide the value by 1000 - but your local settings may insert something other than a decimal point! -- Cheers Nigel "Hemant_india" wrote in message ... hi i want to add "." in "123000" as a decimal point if it is not already there how to do this? -- hemu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find...insert
The last code failed a test, try this modification:
Sub thousands() Dim rng As Range, c As Range Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Worksheets("Sheet1").Range("A1:A" & LRow) For Each c In rng If InStr(1, c.Text, ".") = 0 Then c.Value = c.Value / 1000 c.NumberFormat = "0.000" End If Next End Sub Mike F "Mike Fogleman" wrote in message m... Hemu, try this: Sub thousands() Dim rng As Range, c As Range Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Worksheets("Sheet1").Range("A1:A" & LRow) For Each c In rng If InStr(1, c.Value, ".") = 0 Then c.Value = c.Value / 1000 c.NumberFormat = "0.000" End If Next End Sub Mike F "Hemant_india" wrote in message ... thanx nigel i want to put ur code in a loop and want to check whether "." already exist in string how do i do that actually i'm importing a sequentil thru import wizard...with fixed width -- hemu "Nigel" wrote: Inserting an additional character into a string can be achieved by splitting the string into two and re-combine with the additional(s) required in its simplest form....... stOriginal = "123000" stResult = Left(stOriginal,3) & "." & Right(stOriginal,3) if it will be numerical then you could just divide the value by 1000 - but your local settings may insert something other than a decimal point! -- Cheers Nigel "Hemant_india" wrote in message ... hi i want to add "." in "123000" as a decimal point if it is not already there how to do this? -- hemu |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find...insert
thanx mike
thanx a lot -- hemu "Mike Fogleman" wrote: The last code failed a test, try this modification: Sub thousands() Dim rng As Range, c As Range Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Worksheets("Sheet1").Range("A1:A" & LRow) For Each c In rng If InStr(1, c.Text, ".") = 0 Then c.Value = c.Value / 1000 c.NumberFormat = "0.000" End If Next End Sub Mike F "Mike Fogleman" wrote in message m... Hemu, try this: Sub thousands() Dim rng As Range, c As Range Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Worksheets("Sheet1").Range("A1:A" & LRow) For Each c In rng If InStr(1, c.Value, ".") = 0 Then c.Value = c.Value / 1000 c.NumberFormat = "0.000" End If Next End Sub Mike F "Hemant_india" wrote in message ... thanx nigel i want to put ur code in a loop and want to check whether "." already exist in string how do i do that actually i'm importing a sequentil thru import wizard...with fixed width -- hemu "Nigel" wrote: Inserting an additional character into a string can be achieved by splitting the string into two and re-combine with the additional(s) required in its simplest form....... stOriginal = "123000" stResult = Left(stOriginal,3) & "." & Right(stOriginal,3) if it will be numerical then you could just divide the value by 1000 - but your local settings may insert something other than a decimal point! -- Cheers Nigel "Hemant_india" wrote in message ... hi i want to add "." in "123000" as a decimal point if it is not already there how to do this? -- hemu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find name and insert address | Excel Worksheet Functions | |||
Find and insert? | Excel Programming | |||
Find & Insert - Is this possible?? | Excel Discussion (Misc queries) | |||
How do I find a value and insert new value | Excel Worksheet Functions | |||
find row value, insert column | Excel Programming |