Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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

Find all numbers in a text string with extracting numbers only

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. Full Feature Free Trial 60-day!

ad split cell separate text numbers

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


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.

note ribbon 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

doc find first all numbers 1

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:

doc find first all numbers 3

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.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

doc find numbers 01

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

doc find first all numbers 6

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

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


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

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

doc find first all numbers 10

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:

doc find first all numbers 11

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 - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.