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

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

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