Here's a UDF (I use) - Place this into a Standard Module

Public Function ExtractNums(c) As String
Dim i As Integer
Dim MyNums As String

'Templatebuilder
'Returning numeric value from string'
MyNums = ""
For i = 1 To Len(c)
If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) 0 Then
MyNums = MyNums + Mid(c, i, 1)
End If
Next i
ExtractNums = MyNums
End Function




"Fiona" wrote:

Hi
I wondered if anyone knew the formula to extract a block of numbers in
amongst a sentace, the numbers are a a different place in each line
(mr joe bloggs 12456
mrs smith hometown county 75864
mr tom jones county potcode 44456)

the numbers always contain 6 digits.

Thank you!!