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 which 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 function 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 Number 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. On similar lines, we would like to know the position of first digit/number in the string.
Suppose we have data as shown below:
Use the following formula in cell B2 to get to know from where the extraction/split starts. Formula given below results in giving the position of the first number appearing in 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 denotes the position of first digit appearing in a string present at each cell of column A.
Use a combination of RIGHT and LEN function to extract the numbers out of string in column C. Put 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 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 result.
Drag the formula until the row number five of column C.
If you could have noted in the results present in column C, all the numbers are left-aligned. Which means those are being stored as text in column C. This is because we have used formulae such as RIGHT, LEN to find out the numbers from given text. 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 – It will automate the system and once the code is done and set, you don’t again need to edit it. 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 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 you wanted to extract number from.
- Third and the final part contains the loop which allows you to extract numbers from given string and stores it under a given column.
Run this code by hitting 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.
- The benefit of this VBA code is, 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 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 VALUE function inside which all the formula should be entered.
- 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. That formula only works when your numerical values are at 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 downloadable excel template. You can also go through our other suggested articles
