try this macro and check whether you get what you want
Option Explicit


Public Sub test()
Dim i As Integer
Dim j As Integer
'ActiveCell.FormulaR1C1 = "=LEN(R[-8]C)"
j = Len(Range("c4").Value)
MsgBox j
Dim mystring As String
Dim result As Range
Set result = Range("a10")'****************

mystring = Range("c4").Value

Dim x1 As String, x2 As String, x3 As StdFont, x4 As String
i = 1
line1:
result = Mid(mystring, i, j / 4)

Set result = result.Offset(1, 0)
i = i + j / 4
If i j Then Exit Sub
GoTo line1
End Sub

if successful change
the line marked ******* to suit you.



Himu wrote in message
...
Peo:

the problem i am facing with using "MID" function is that, I have to
specify
the start start_num and num_chars. But I want somthing that will divide
the
numbers in the cell in 4 parts without having me to manually type the
start_num and num_chars.

My actual problem is that I have a number with 164 digits in one cell and
i
want to divide them into parts of 4 digits in each parts. I just simplied
my
question to a 16 digits number. Hope you understand why it is difficult
for
me to manually type the start_num and num_chars.


Thankx!

HIMU

"Peo Sjoblom" wrote:

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Himu" wrote in message
...
Hi:

Suppose I have 16 numbers in a cell (A1), e.g. 1234567890123456. Now I
want
to spit the numbers in 4 parts for that I get the numbers 1234 in cell
A2,
5678 in cell A3, 9012 in cell A3 and 3456 in cell A4.

Anyone have any idea how I can do this?

Thank you.

Himu.