There have been several posts here on using functions or user defined functions (UDFs) to extract text or numbers from a longer string (most recently, here), but for many purposes it is quicker and easier to use the “flash-fill” feature, introduced in Excel 2013.
The examples below show examples of extracting a number from the end of a text string, using data copied and pasted from Strand7 as an example:
In the first example it is required to extract the numbers from the text in column B. Simply enter the first number in the adjacent column, then press Ctrl-E (or click Data-Flash Fill):
The column is immediately filled with the required values. A small icon appears next to the top cell, which gives the options displayed above if selected.
It is also possible to use a non-adjacent column:
Note that in this case the values extracted are not in ascending order.
More complex examples also work:
In this case the values in rows 14 to 17 (5-1 to 5-4) have been extracted as a date. This can be fixed by simply formatting the example cell as text:
Also if the example cell is changed immediately, the flash-fill automatically updates:
There are however times when more control is required. The screen-shot below shows results from flash-fill (in column A), compared with the ExtractNums UDF in Column D (download here).
Thanks for the reminder to look into this feature. I get so used to tackling a problem the way I’ve done it before – because it worked – I neglect to look at new methods. A gentle nudge is welcomed. And the last example illustrates the need to always check results.