By the way, for a more lazy but shorter code to the original post, one can use this for let's say, text that don't have more than 25 characters

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25)) +1,1)*10^ROW($1:$25)/10)

Picked it from the link posted here

How to separate numbers and words | Chandoo.org Excel Forums - Become Awesome in Excel