The colum below is named 'REMARK'. I need a macro to extract the
cheque numbers (e.g 100138773) into different column and the names
(e.g KONNY BALL) into another column.

REMARK
========
CHQ 10013873 IFO KONNY BALL
chq10013845 ifo John Bush
chq no 10013848 pd ifo racheal
CHQ#10013928 PD IFO FRANK

You can do it with formulae

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

and

=MID(A1,SEARCH("ifo",A1)+4,99)

--
__________________________________
HTH

Bob

"sylink" wrote in message
...
The colum below is named 'REMARK'. I need a macro to extract the
cheque numbers (e.g 100138773) into different column and the names
(e.g KONNY BALL) into another column.

REMARK
========
CHQ 10013873 IFO KONNY BALL
chq10013845 ifo John Bush
chq no 10013848 pd ifo racheal
CHQ#10013928 PD IFO FRANK

Consider:

Sub splitum()
Dim s As String
s = "0123456789"
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
l = Len(v)
j = ""
k = ""
For ii = 1 To l
ch = Mid(v, ii, 1)
If InStr(s, ch) 0 Then
j = j & ch
Else
If j = "" Then
Else
GoTo donewd
End If
End If
Next
donewd:
Cells(i, 2).Value = j
Cells(i, 3).Value = Mid(v, ii, 256)
Next
End Sub
--
Gary''s Student - gsnu2007L


"sylink" wrote:

The colum below is named 'REMARK'. I need a macro to extract the
cheque numbers (e.g 100138773) into different column and the names
(e.g KONNY BALL) into another column.

REMARK
========
CHQ 10013873 IFO KONNY BALL
chq10013845 ifo John Bush
chq no 10013848 pd ifo racheal
CHQ#10013928 PD IFO FRANK