Hi,

It worked well.
Thanks,

"kounoike" wrote:

select the cell which contains data, and run mytest.
it will put out strings enclosed by "[]" below that cell, maybe. but not sure to
statify your condition.

Sub mytest()
Dim i As Long
If Not IsError(Mysplit(ActiveCell)) Then
For i = LBound(Mysplit(ActiveCell)) To UBound(Mysplit(ActiveCell))
ActiveCell.Offset(i + 1, 0) = Mysplit(ActiveCell)(i)
Next
End If
End Sub

Function Mysplit(ByVal s) As Variant
Dim tmp
Dim s1 As String
Dim i As Long
Dim instate As Boolean
Const del1 = "["
Const del2 = "]"

instate = False
i = 1
Do
s1 = Mid(s, i, 1)
If instate And s1 < del2 Then
tmp = tmp & s1
ElseIf s1 = del1 Then
instate = True
ElseIf s1 = del2 Then
instate = False
tmp = tmp & Chr(1)
End If
i = i + 1
Loop While (i < Len(s))
If Right(tmp, 1) = Chr(1) Then
tmp = Left(tmp, Len(tmp) - 1)
End If
Mysplit = split(tmp, Chr(1))
End Function

keizi

"Nagesh" wrote in message
...
I have a value say @Sum([aa] [bb] [cc] [dd] [ee]) in cell A1.
I want to place the cursor in cell A2 and run a function which will remove
the brackets and place the values in cells as follows:
A2- aa
A3- bb
A4- cc
A5- dd
A6- ee

Thanks, Nagesh