I was initially debating whether to give this post a more pragmatic title, such as “Extracting Phone Numbers from a String”, that being one of the more common practical applications for the techniques outlined here.
However, the extraction of phone numbers (I’m referring here to that type which employs some form of delimiter, e.g. 1-800-12345, and not that which comprises a non-delimited numerical string, e.g. 180012345, there existing already well-documented formula techniques for the extraction of the latter – although of course the set-up given here will work for those as well) is certainly not the only use for this method, and so, in the end, I chose to go with a less restrictive, more theoretical title.
In this post I would like to present a solution to the practical problem of extracting a number of defined length from an alphanumeric string which may contain several numbers of varied lengths.
Indeed, the inspiration behind this post is in part derived from having personally witnessed many such requests on the various Excel forums, most of which involve the extraction of e.g. an account number of fixed length, 6 digits, say, from a longish string containing many other numbers.
As an example, given the following string:
20/04/15 - VAT Reg: 1234567: Please send 123456 against Order #98765, Customer Code A123XY, £125.00
we may wish to extract the one occurrence of a 6-digit number (123456) from that string.