When we analyze data as students, teachers, or professional analysts, at one point, we'll want to extract certain numbers from alphanumeric strings. But the disappointing part is that Excel doesn't have a single-tap feature for this, making it a difficult task. So, of course, analysts get stuck here or pay other analysts to help them.
However, in this article, you will learn how to easily extract only numbers from a cell with text and numbers.
How to Extract Numbers from Cells With Text and Numbers in Excel
When I first used Microsoft Excel for my data analysis as a student, I pretty much knew my way around its user interface and functions. But I didn't know how Excel extracts a number from a cell with text and number until some extra tutors, which I will discuss and simplify here.
Method 1. From the Beginning of the Text String
I'm starting off with this one because it is the easiest of all the steps. It's the best method if the data in the cell you want to extract has numbers before the text. The most important component of this step is the LEFT ( ), ROW, INDIRECT, and LEN functions. You will need these functions to input the number extraction formula.
To extract numbers from the beginning of a text string, enter this formula in an empty cell:
=LEFT(cell_reference, MATCH(FALSE, ISNUMBER(MID(cell_reference, ROW(INDIRECT("1:"&LEN(cell_reference)+1)), 1) *1), 0) -1)
Then, hit the Enter button on your keyboard. Note that the "cell_reference" in the formula refers to the cell ( containing the text string with the number you want to extract.
Of course, this may look challenging, but it is straightforward. Let me explain better with an example backed by an image. For example, from the image below, cell A9 contains the text string "06-EACD-04," and you want to extract the starting number "06;" Enter the formula with reference to the cell in question.
Just like this:
=LEFT(A9, MATCH(FALSE, ISNUMBER(MID(A9, ROW(INDIRECT("1:"&LEN(A9)+1)), 1) *1), 0) -1)
This formula will extract the first number (06) before the text in the cell. No matter how many digits are present in the middle or at the end of the string, by using this approach, you can extract the numbers from the beginning. This method is very dependable, simple, and easy to use, which makes it a great option for data extraction jobs.
But you can have trouble using this method to extract numbers from a string if there are issues with the spacing and parenthesis. Thus, you must depict each spacing when necessary and note how you arrange the parenthesis to avoid getting an error response.
Method 2: From the End of the Text String
Sometimes, our data cell may have all the numbers at the end of the string, i.e., after the text. In such cases, you can start from the end of the string to extract numbers from a mixed text in Excel. The fastest way to do this is to use the formula below to supply the RIGHT ( ) function with the number of characters for extraction.
To extract numbers from the end of a text string, enter this formula in an empty cell:
=RIGHT(cell_reference, LEN(cell_reference) - MAX(IF(ISNUMBER(MID(cell_reference, ROW(INDIRECT("1:"&LEN(cell_reference))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell_reference))), 0)))
Remember that "cell_reference" refers to the cell containing the text string with the number you want to extract. Then, press the Enter button to execute your command.
Let's make it more illustrative using the same text string I used in the first method. Consider the cell_reference to be A9 with the string "06-EACD-04." The aim is to extract 04 from the cell, and we can do this with the formula:
=RIGHT(A9, LEN(A9) - MAX(IF(ISNUMBER(MID(A9, ROW(INDIRECT("1:"&LEN(A9))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A9))), 0)))
This formula extracts numbers from a string in Excel from the back, and in this case, it extracts 04 from the string and ignores 06 and EACD.
This method shares similarities with the first one, and the only difference is that it extracts numbers from the end of the string instead. Suppose you want to analyze Product IDs or serial numbers; this method is your best bet to extract numbers from the back of the data in each cell.
Method 3: From Any Position in the Text String
Some data cells appear without a pattern, i.e., the numbers and letters are mixed in a way that makes them neither in the beginning, middle, or end of the text. The formula below in Excel extracts numbers from cells with text and numbers even as they're mixed:
=SUMPRODUCT(MID(0&cell_reference, LARGE(INDEX(ISNUMBER(--MID(cell_reference, ROW(INDIRECT(“1:”&LEN(cell_reference))), 1)) *
ROW(INDIRECT(“1:”&LEN(cell_reference))), 0)), ROW(INDIRECT(“1:”&LEN(cell_reference))))+1, 1) *10^ROW(INDIRECT(“1:”&LEN(cell_reference)))/10)
As always, let's have a better illustration with an example. An example is if you want to extract numbers from a string in Excel at cell A9 containing the data "06-EACD-04." You can simply replace "cell_reference" above with "A9". i.e.:
=SUMPRODUCT(MID(0&A9, LARGE(INDEX(ISNUMBER(--MID(A9, ROW(INDIRECT("1:"&LEN(A9))), 1)) * ROW(INDIRECT("1:"&LEN(A9))), 0), ROW(INDIRECT("1:"&LEN(A9))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A9)))/10)
Note that this formula might be a hassle for beginners, so I suggest you copy and paste it into Excel to extract numbers from any position in the cell. Also, if the cell you input does not contain a number, this action will return as 0.
Frankly, I didn't understand this method at first glance. But after several personal trials, I got the hang of it. I think, among the three methods, this is the most complex way to extract numbers from a string in Excel. However, it is the easiest way to extract numbers from a data string, such as the quantity of a product or the price of a service.
Additional Tips:
Test each formula on a small dataset to ensure it works as expected before applying it to a larger dataset.
To avoid mistakes when writing the formula, copy (CTRL+C) and paste (CTRL+V) it on your spreadsheet, but replace the "cell_reference" with the exact cell you want to extract from.
Always keep a backup of your original data.
A Useful Tool - WPS Office
WPS Office is a top tier among the list of interesting analysis tools today! You must have heard of MS Office and all the tasks that its countless features can perform. Let's start by painting a picture in our mind before I dive into what WPS Office is all about. Imagine having a tool that works like MS Office but comes for free! Interesting, right?
WPS Office suite is a free tool that can professionally perform all your documenting, editing, processing, and presenting tasks. It is an all-in-one suite with exciting features, including PDF editors, word processors, spreadsheets, and many more.
For those looking for a free alternative to MS Office, I recommend the WPS Office suite– because it's the best choice. Whether you are a student, teacher, office worker, or an enthusiastic editor, WPS Office can make your work more efficient, visually appealing, and professional. Here's why:
WPS Office high compatibility: With the WPS suite, you can easily open and edit files from different platforms, including Microsoft Office.
WPS Office has interesting, modern, and unique features with equal efficiency to MS Office's features. Its word processor, presentation slides, PDF editor, and many more are top-class!
WPS Office is available for you, no matter the type of operating system your device uses. Your Windows, Mac, Android, Linux, iOS, and other operating systems can efficiently use WPS Office.
How to download WPS Office
Earlier, I mentioned that WPS is available for free download for all devices. If you want to experience the beauty of the suite today, let me explain how you can download it:
Step 1: Visit the WPS office website at https://www.wps.com/
Step 2: Click the "Free Download" button and choose the WPS Office version compatible with your device.
Step 3: After completing the download, open the installer and follow all prompt instructions to install the app on your device.
How to Edit Excel in WPS
WPS Office's compatibility enables it to view, edit, and share files regardless of their format. Excel files are not excluded, and that will be our focus in this section. Here are the steps to take to edit Excel files on WPS:
Open the "Spreadsheet" icon in the WPS Office.
Select the Excel files you want to edit.
Use WPS Office's elegant features to edit, format, and customize the file.
Once you finish making the changes, hit the "Save" button to secure your edits.
Use Word, Excel, and PPT for FREE, No Ads.
Edit PDF files with the powerful PDF toolkit.
Microsoft-like interface. Easy to learn. 100% Compatibility.
Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.
FAQs
How Do I Separate Texts and Numbers?
With the Text to Columns feature, you can separate texts from numbers in Excel. With this feature, you can split a cell from its delimiter, such as a comma or space. To streamline your dataset, you can retain only the columns with numeric values and remove the ones that don't.
How Do You Split Numbers in Excel Formulas?
You can split numbers in Excel formulas with: =MID(A1, COLUMN()-(COLUMN(C1)-1),1). Where A1 is the cell with the number that you want to split the digits, while C1 is the cell for locating the first split digits.
Summary
Data analysis gets tricky when you want to extract only numbers from a cell with texts and numbers. However, the guide I provided above makes the process less tedious.
Also, if you need a better suite to analyze your data and perform other document tasks, there's no better option than WPS Office. It is efficient, easy to use, compatible with numerous file formats, and overall, it is free! Get your WPS Office suite today and expand your creative widths.
