https://trumpexcel.com/extract-numbers-from-string-excel/

This formula will work only in Excel > 2016 as it uses the newly introduced TEXTJOIN function.
Note that the TEXTJOIN formula covered in this section would give you all the numeric characters together. For example, if the text is “The price of 10 tickets is USD 200”, it will give you 10200 as the result.

This is an array formula, so you need to use ‘Control + Shift + Enter‘ instead of using Enter.
In case there are no numbers in the text string, this formula would return a blank (empty string).

Data is column A, starting at A2
Furmula in column B, starting at B2
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

Pro Tip: If you want to check the output of a part of the formula, select the cell, press F2 to get into the edit mode, select the part of the formula for which you want the output and press F9. You will instantly see the result. And then remember to either press Control + Z or hit the Escape key. DO NOT hit the enter key.

Formula in column C, starting at C2
=NUMBERVALUE(B2)

======================================
You can also use the same logic to extract the text part from an alphanumeric string. Below is the formula that would get the text part from the string:
Caution: While this formula works great, it uses a volatile function (the INDIRECT function). This means that in case you use this with a huge dataset, it may take some time to give you the results. It’s best to create a backup before you use this formula in Excel.

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

======================================
Extract Numbers from String in Excel (for Excel 2013/2010/2007)
Although this is an array formula, you don’t need to use ‘Control-Shift-Enter’ to use this. A simple enter works for this formula.

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

======================================
Separate Text and Numbers in Excel Using VBA
See URL