To extract number from an alphanumeric string, the first thing you need to know is where to start the extraction. In Excel 2019 and earlier, it only works as an array formula, so remember to press Ctrl + Shift + Enter to complete it. In Dynamic Array Excel (Office 3), you enter the formula in the usual way with the Enter key.
To catch errors in the strings that do not contain a single number, use the IFERROR function. If you need the result to be a number (that you can use in further calculations), then wrap the formula into the VALUE function or perform an arithmetic operation that does not change the result, say, multiply by 1 or add 0. In our case, the result is a numeric substring, which in terms of Excel is also text, not number. The output of this function is always text. The extraction is performed with the RIGHT function that belongs to the category of Text functions. If a string also has numbers in the beginning or middle, they are ignored: This formula gets number only from the end. For now, simply replace cell with a reference to the cell containing the original string (A2 in our case), and enter the formula in any empty cell in the same row, say in B2:
We will dwell on the formula's logic a bit later. When you have a column of alphanumeric strings where number comes after text, you can use the following formula to get it.
How to extract number from the end of text string How to extract number from string with Ultimate Suite.Extract number from anywhere in a string.Extract number from the right of a string.
Below you will find full details on both methods. Or, you can run the Extract tool and have the job done with a mouse click. To get a number from a string in Excel, it takes a little ingenuity, a bit of patience, and a bunch of different functions nested into each other. When it comes to extracting numbers from an alphanumeric string, Microsoft Excel provides… nothing. When it comes to extracting part of a text string of a given length, Excel provides three Substring functions (Left, Right and Mid) to quickly handle the task. The tutorial shows how to extract number from various text strings in Excel by using formulas and the Extract tool.