Extract Numbers From String (Table of Content)
Introduction to Extract Numbers From String
Excel is a tool that is very versatile and compact at the same time. When it comes to extracting certain characters from a text string, it provides a range of formulas such as LEN, RIGHT, LEFT, SEARCH, using which individually or in combination with each other, we can find out the character required. However, when it comes to extracting numbers from a string that contains a mixture of characters and numbers, Microsoft Excel fails to give any concrete formula for the same. However, it is not impossible at all to extract numbers from a string. It is definitely not as easy as extracting characters from string but at the same time not impossible. All you need to have is patience, some combination of different functions nested within each other. It will take you through. Let’s take some examples and see how we can extract numbers from a text string.
How to Extract Numbers from String in Excel?
To Extract Numbers from String is very simple and easy. Let’s understand how to Extract Numbers from String in Excel with some examples.
Example #1 – How to Extract Numbers Positioned from Right
As you might have seen while extracting characters from a string, the most important thing we should know is the position of the last character. Once it is found, we can use the RIGHT function to extract the characters from the text. We would like to know the first digit/number position in the string on similar lines.
Suppose we have data as shown below:
Use the following formula in cell B2 to get to know from where the extraction/split starts. The formula is given below results in giving the position of the first number appearing in the mix string.
After using a formula in cell B2, the output is shown below.
Drag this formula up to row number six. You’ll see the results as below:
Numbers in each cell of column B denote the first digit’s position appearing in a string present at each cell of column A.
Use a combination of RIGHT and LEN functions to extract the numbers out of the string in column C. For example, put the following formula in cell C2 and press Enter key.
After applying a formula to all the cells, you should get an output as follows.
Here in this formula, we wanted to extract only the numbers from the string present in cell A2. Also, all the numbers are on the right-hand side of the cell. Therefore, we use the RIGHT function initially with A2 as an argument. In this function, we used the LEN function, which takes a length of A2 and subtracts B2 (which contains the first position of number in the string). We also add 1 in it so that the first digit also gets included under the result.
Drag the formula until row number five of column C.
If you could have noted in the results present in column C, all the numbers are left-aligned. This means those are being stored as text in column C. We have used formulae such as RIGHT and LEN to find out the numbers from the given text. So, in order to convert all the text values present in each cell of column C, follow the next step.
Example #2 – VBA Code to Extract Numbers through Mixed String
What we just did can be done on similar lines using a Macro/VBA code as well. The advantage of macro would be that it will automate the system, and once the code is done and set, you don’t need to edit it again. Just use the same macro again and again for a different set of mixed strings to extract the numerical values.
Go to the Developers tab and click on Visual Basic.
Click on the Insert tab present on the upper ribbon and open a new Module through it.
Define a new sub-procedure under which we can store the macro.
Now, copy the following code and paste it under the given sub-procedure.
Here, the code is divided into three parts –
- First is the initialization of the variable, which is going to hold the numerical values extracted from the string.
- Under the second part, you need to run through the range under which the values are present and extract numbers.
- Third and the final part contains the loop, which allows you to extract numbers from a given string and stores them under a given column.
Run this code by hitting the F5 or Run button placed on the upper pane.
You’ll see the output under column B of your worksheet. Which only contains numbers extracted from the actual string.
- This VBA code’s benefit is that it works on any string with having a position of numbers anywhere. In between. While the formula used in the first example is limited to the strings in which the numbers are at the right of the string.
- These are the two ways using which we can capture and extract the numbers from a given mixed string. I assure you there are many more. But these two are the ones this article has covered.
Let’s wrap the things with some things to be remembered.
Things to Remember About Extract Numbers From String
- In the first example, all we used are text functions like RIGHT and LEN. The results provided by these formulae will be text only (even numbers would be stored as texts). If you want them to convert into actual numerical values, use the VALUE function to enter all the formulas.
- The formula method is a bit tricky but can work on thousands of cells at the same time. However, it lacks versatility. Like the one we used in the first example. In addition, that formula only works when your numerical values are on the right-hand side of your actual string.
- Using VBA macro, you can get the results just in a single click. Irrespective of the position where your number belongs in an actual string. This is the advantage of macro (But you need to be decent enough to write a macro to automate the processes).
Recommended Articles
This is a guide to Extract Number From String. Here we discuss How to Extract Number From String in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles.
23 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion
4.9
View Course
Related Courses
