Try something like this:

Sub test()

Dim i As Long
Dim c As Long
Dim n As Long
Dim arr
Dim arr2
Dim arr3

'test data range
arr = Range(Cells(1), Cells(5, 1))

ReDim arr3(1 To UBound(arr), 1 To 1)

For i = 1 To UBound(arr)
arr2 = Split(arr(i, 1), Chr(32))
For c = 0 To UBound(arr2)
If Len(arr2(c)) = 7 Then
If IsNumeric(arr2(c)) Then
n = n + 1
arr3(n, 1) = arr2(c)
End If
End If
Next c
Next i

Range(Cells(3), Cells(UBound(arr), 3)) = arr3

End Sub


RBS


"anon" wrote in message
...
Hi,

I have a list of cells, each containing address, tel. no. and customer
ID for a specific customer.

I'm looking to find the customer ID from a each cell and copy to
another cell using vb.

The customer ID could appear at the beginning, middle or end of the
cell contents, however is always 7 digits long with a space at each
side of it (unless it is the first part of the cell contents in which
case there would be no space at the beginning). The complexity is that
the cell also contains telephone numbers, however these are longer
than 7 digits.

Your help / ideas on how I could start tackling this would be much
appreciated. Thanks,