Jake

fair comment. I had considered multiple spaces before the number itself and
it will work for that. However, additional spaces in the text would result
in #VALUE

Let's try this:

=VALUE(MID(RIGHT(A3,6),FIND(" ",RIGHT(A3,6))+1,LEN(RIGHT(A3,6))-FIND("
",RIGHT(A3,6))))

It only works because the OP said there were 3 to 5 digits after the space.

It copes with "te xt 321", "te x t 321" and "te xt ab 12345"
so I think it will work for everything.

Regards

Trevor


"Jake Marx" wrote in message
...
Hi Trevor,

"Trevor Shuttleworth" wrote in message
...
you could just use a formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)))

But this won't work if there are space(s) in the text before the final
space. I gave up (albeit fairly quickly) trying to come up with a simple
function to do this.

Regards,

Jake Marx
MS MVP - Excel