## How to find first/all numbers in a string in Excel?

Supposing a cell is mixed with letters, numbers, and other characters, how could you quickly find out the first number or all numbers from this cell in Excel? This article describes three tricks to solve it easily.

#### Find first number and its position in a text string with formula

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.

#### Find all numbers in a text string with removing all non-numeric characters

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.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now

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.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now

#### Find all numbers in a text string with extracting numbers only

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

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now

#### Batch Separate text and numbers from one cell/column into different columns/rows

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.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now