In your examples, the numbers are always at the end.

If that be the case, try:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

If you want them to be true numbers, add the dbl unary:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Fiona" wrote in message
...
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!!