Skip to main content

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.
doc find first all numbers 1

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

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.


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:
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 - 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:
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 - 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.


ad split cell separate text 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

Demo: find all numbers in a string in Excel


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thak you very much.

Marko
This comment was minimized by the moderator on the site
Hi.

Is possible to sum all WA11?

WA11 4
AdBlue 1, WA11 2
AdBlue 3, WA11 3, shift 4

... and everything is in one column
This comment was minimized by the moderator on the site
Hi there,

Suppose that your data above is in A1, A2, A3.
You can enter the formula in B1: =VALUE((LEFT(IFERROR(RIGHT(A1,LEN(A1)-(FIND("WA11 ",A1,1))-LEN("WA11 ")+1),""),1)))
And then drag the fill handle down to apply the formula to below cells.
At last, use a SUM function to add all the results together.

Note that this formula will only get the first number after WA11.

Amanda
This comment was minimized by the moderator on the site
Hi
Note that this formula will only get the first number after WA11.

Is possible to make formula that sometimes use 1 to max 4 number after wa11, depend how much number is after wa11?

Thanks for your help
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations