This method will introduce some formulas to find and extract the first number in a text string, and find out the position of the first number in the text string as well.

Find and extract the first number in a text string with an array formula

Select a blank cell where you want to return the first number from a text string, enter the formula =MID(A2,MIN(IF((ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0)*ROW(INDIRECT("1:"&LEN(A2)))),ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0)*ROW(INDIRECT("1:"&LEN(A2))))),1)+0 (A2 is the text cell where you will look for the first number ) into it, and press the Ctrl + Shift + Enter keys simultaneously. And then drag this cell’s Fill Handle to the range as you need.

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more…     Free trial

Then the first numbers of each text string are found and extracted as below screenshot shown:

Find the first number’s position in a text string with formulas

Select a blank cell where you will return the first number’s position, and enter the formula =MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))) (A2 is the text cell where you will look for the first number’s position) into it, and then drag its Fill Handle to the range as you need. See below screenshots:

Note: This array formula =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&{0,1,2,3,4,5,6,7,8,9})) also can get the first number’s position. After entering this array formula, please press the Ctrl + Shift + Enter keys simultaneously to get the result.

If all characters can be removed from a text string except the numbers, we will find out all numbers in this text string easily. Kutools for Excel’s Remove Characters utility can help you solve it easily.

1. Select the range that you will find all numbers in each text cell, and click the Kutools > Text > Remove Characters. See screenshot:

2. In the opening Remove Characters dialog box, only check the Non-numeric option, and click the Ok button.

Now all non-numeric characters are removed from selected cells, and remained only numbers.

Actually, Kutools for Excel’s EXTRACTNUMBERS function can help you extract all numbers from a text cell easily.

1. Select a blank cell you want to output the extracted numbers, and click the Kutools > Functions > Text > EXTRACTNUMBERS. See screenshot:

2. In the opening Function Arguments dialog box, specify the text cell into the Txt box, type TRUE into the N box, and click the OK button. See screenshot:

Notes:
(1) It’s optional to type TRUE, FALSE, or nothing into the N box. If you type FALSE or nothing into the N box, this function will return numbers stored as text, while TRUE will return numerical numbers.
(2) Actually, you can enter the formula =EXTRACTNUMBERS(A2,TRUE) into the blank cell directly to extract all numbers from the selected cells.

Now you will see all numbers are extracted from each text cell as below screenshot shown:

Kutools for Excel enhances its Split Cells utility and supports to batch separate all text characters and numbers of one cell/column into two columns/rows.