Try this:

Sub GetNumber()
Dim v As String, s1 As String, s2 As String
Dim l As Integer, ll As Integer
Dim r As Range, rr As Range
Set r = Intersect(ActiveSheet.UsedRange, Range("F:F"))
For Each rr In r
v = rr.Value
l = Len(v)
s2 = ""
For ll = 1 To l
s1 = Mid(v, ll, 1)
If IsNumeric(s1) Then
s2 = s2 & s1
If Len(s2) = 6 Then
rr.Offset(0, 4).NumberFormat = "@"
rr.Offset(0, 4).Value = s2
Exit For
End If
Else
s2 = ""
End If
Next
Next
End Sub
--
Gary''s Student - gsnu201001


"Len" wrote:

Hi,

I need help to find the solution on how to set excel vba code to
extract 6 digits numeric value ( ie it may start at zero value follow
by the number or no number given ) from text string

E.g.

VBA codes look for 6 digits numeric value from column F and extract it
to column J

Column
F
Column J
a) CIMB - DA dd 11/12/09 - Underpaid for cheque : 632005 dd 10/12/09
----- 632005
b) HSBC - CA dd 7/12/09 - Overpaid for cheque : 005946 dd 03/12/09
------ 005946
c) RHB - Expiry chq tsfr to unclaimed a/c - 329720 dd 1/6/09 - Leeyana
------ 329720
d) PBB - Expiry chq tsfr to unclaimed a/c - 090813 dd 10/3/09 - Yap KC
----- 090813
e) UHB - DA dd 17/12/09 - Underpaid for cheque dd 14/12/09
--------------------- blank

Any helps will be appreciated and thanks in advance

Regards
Len
.