I have previously posted a spreadsheet with user defined functions (UDFs) using regular expressions to extract numbers from a text string (see Extracting numbers from text and regular expressions).

I have now modified the Extractnums function to optionally return the numbers as text strings, rather than values. This allows for instance, for telephone or account numbers to be extracted without corruption, or dates in number format to be returned as a string to avoid problems with differing regional date formats.

The spreadsheet (including open source code) can be downloaded from:

RegExpres.xlsb

The screenshot below shows output from the ExtractNums function with the Returnstrings argument omitted (default = False), and set to True (click on image for full size view):

The optional arguments to the function are:

• ReturnStrings: return all numbers as strings; default = False
• Position: return the number at the specified position; default = 0 = return all numbers
• MaxNum: maximum number of values to extract from the string; default = 10
• DecString: decimal point string; default = “.”
• IgnoreString: character to be ignored if surrounded by two numbers; default = “,”

Note that in columns F to I no optional arguments have been given, and the input range includes rows 18 to 30.  The function returns a table of the extracted numbers, using all the default settings.  In Columns K to N the ReturnStrings argument has been set to True, and a separate function has been entered for each line, so that different optional arguments can be applied on each line.