Extracting numbers from text – update

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:


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.

This entry was posted in Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

1 Response to Extracting numbers from text – update

  1. Pingback: Using Flash Fill | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.