extracting digits only from column with letters and digits

I have a column of data (2000 rows) containing both letters and digits eg Newman, "Paul", "0405925621" and I want to get the phone number into a seperate column. Any easy way?

Hi Jayne - here is one way. Assuming your data starts in A1

=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),15)

I entered forumula in column B and copied down the rows and it worked for cells that only had digits, but for the other cells the result is #VALUE!.
437801005 437801005
415506645 415506645
413484212 413484212
419597411 419597411
419325860 419325860
Brown,"Susan","0407505365john" #VALUE!
Arnott,"John","018916934" #VALUE!

Ok give this a try..

=LOOKUP(99^99,--(MID($A1,MATCH(TRUE,INDEX((--MID($A1,ROW($1:$255),2))>9,0),0),ROW($1:$255))))

Thanks Jakobshavn, but showing my ignorance here. If I copy and paste the formula into the cell in column B1, 'how' do I ENTER it with CNTRL-SHFT-ENTER?

---------- Post added at 10:23 AM ---------- Previous post was at 10:21 AM ----------

Thanks heaps day92. This worked for most of the cells. The only cells it didn't work for were those that had spaces in the phone number eg.
Dee,"Claire","0437 821 859" 437
There aren't too many of them out of the 2,300 so I can do them manually if needed.

---------- Post added at 10:29 AM ---------- Previous post was at 10:23 AM ----------

Actually, one other thing - when I used day92 LOOKUP formula, the results ignored the 0 at the start of the number eg
Jarrel,"Peta","0407140396" 407140396

Is there someway I can either bring that into the formula OR an easy way to add it to the result in column B?
Perhaps I do need to understand how to do Jakobshavn array formula to get the best result?

Hi Jakobshavn,
I've been trying to manually enter the formula so I could hold CNTRL and SHFT when I ENTER, but it is then highlighting the 1 at then end of this sequence ((-MID("01"&A1,ROW($1:$300),1)), as an error. The following is what I manually entered:

=--MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

Also, is there a way to 'paste' (Ctrl + V) an array formula? I tried holding down Ctrl + Shift, then V but that didn't work.

Hey - got the array forumla to work! THANK YOU.
One last question though. The result is still ignoring the 0 at the start of the number. Now that I have all the digits (without the 0) in column B, is there an easy way to ADD the digit 0 to the beginning of every number in column B?

Awesome - everything working. Thank you so much.

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.