Thankfully, it’s possible to do exactly that, although it can be more complicated than you might first think. Here’s how to extract a number from a string in Excel.
How to Extract a Number from the End of a String Using Flash Fill
A quick and easy way to extract a number from the end of a string is to use Excel’s Flash Fill feature. This feature allows you to quickly fill a range of cells based on the pattern detected in one specific cell. One downside of this method is that it requires you to do the first extraction yourself and if you only want to extract a number from a single string, it won’t be of any use. In most cases, Flash Fill can correctly extract the number from the end of a string, although it is not infallible, so you should always check the results carefully. If it doesn’t work correctly, try manually filling the first two or three cells rather than just the first one. To extract a number from the end of a string using Flash Fill:
How to Extract a Number from the End of a String Using a Formula
One downside of using the Flash Fill method is that it requires you to extract numbers from the string for the first row yourself. It is possible to extract a number from the end of a string using a formula, although the formula is quite complex. To extract a number from the end of a string using a formula: You may be wondering what this formula does. In brief, it generates an array that is the same length as the string. It places each of the characters from the string into this array in order. It then evaluates each of those characters to see if they are a number or not. Any characters that are numbers are converted to zero. Any characters that are not numbers are converted to a number representing its position in the string. For example, AB5HG23 would convert to 1, 2, 0, 4, 5, 0, 0. It then looks for the highest value in this list, which is the position of the last character that isn’t a number. Finally, it returns all the characters after this point, which are the numbers at the end of the string. The VALUE function then converts this back into a number. Simple, right?
How to Extract a Number from the Start of a String Using Flash Fill
You can also use Flash Fill to extract a number from the start of a string by filling in the first example yourself and allowing Excel to stop the pattern. To extract a number from the start of a string using Flash Fill:
How to Extract a Number from the Start of a String Using a Formula
You can also use a formula to extract a number from the start of a string. This works in a similar way to the formula in the previous section but finds the position of the first non-numeric character and returns all the characters before that. To extract a number from the start of a string using a formula:
How to Extract Numbers from Anywhere in a String Using Flash Fill
As mentioned previously, Flash Fill may not work if the first cell doesn’t match the format of some of the other cells. In this case, you may need to manually calculate two or three cells before Flash Fill gives you the result you need. To extract numbers from anywhere in a string using Flash Fill:
How to Extract Numbers from Anywhere in a String Using a Formula
You can use a formula to extract all the numbers from a string, no matter where they appear. To extract numbers from anywhere in a string using a Formula:
Extend Your Excel Skills
Learning how to extract a number from a string in Excel can help you quickly extract the data you need from a list of strings. Whilst the formulas required may be complex, you don’t need to understand exactly how they work; just that they do! There are more useful Excel tricks you can learn to take your skills to the next level. You can learn how to count cells with text in them, or how to calculate someone’s age from their date of birth. You can even create a random number generator in Excel. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
