Extracting numbers from text and regular expressions

Daily Dose of Excel recently had a post on extracting a number from a specified position in a text string, using mega-in-sheet-formulas like:

This reminded me of a post from earlier this year, which provided a User Defined Function (UDF) that used regular expressions to extract numbers from a string.  This worked well if the string contained a single number, but returned incorrect results if there were two or more numbers.

I have now updated this function to return correct results from a string (or array of strings) with two or more separate numbers.  The new function (ExtractNums) is compared below with the previous version (ExtractNum), and another function that extracts numbers from the right hand end of a string.  As well as finding all the numbers, the new function will also deal with negative numbers, numbers with a comma separator for thousands, and numbers in E notation.  The new function may be downloaded from RegExpres.xlsb

In the screenshot below the function returns all the numbers from each string, in an array:

It is also possible to specify a particular number from the sequence to return.  The screenshot below shows results for a range of different number formats, compared with two functions posted in the comments to the DDoE article:

In the screenshot below the third number in the sequence has been requested. When there are less than 3 numbers the function returns #N/A. Note that the Extractnums function recognises 123,456 as a single number, whereas the DDoE functions treat it as two separate numbers.

For speed of operation, The original ExtractNum function (which does no checking of the string returned by the regular expression function) is about 5 times faster than ExtractNums, so is useful for strings that contain a single numerical string.  The F_snb function is about 30% faster than ExtractNums, but does not recognise comma separators, E Notation, or numbers starting with a minus sign.  The F_snb2 function (which is written as a single line of VBA code), is about 10 times slower.

The next post will have a closer look at the code, and present another function for working with regular expressions.