Tag Archives: ISNUMBER

I wouldn’t normally publish a post on such an esoteric topic as this. However, since the idea for it came as a result of a challenge posed by the venerable David Hager, I felt that I could not resist.

And that challenge was as follows: given a list of chemical elements and their respective atomic weights, a formula to determine the weight for a given molecule.

It goes without saying that there are numerous quick and easy online applications which will perform such a calculation. Nevertheless, and however unlikely it may seem, there is still a small probability that this post will reach one or more of the tiny minority who have a practical need for such calculations to be performed within Excel (and, in addition, perhaps without recourse to VBA).

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.

One correct solution received, courtesy of Lori, who not only presented a fine construction for working in Excel 2010 and earlier, but also a 2013 version, which had the added benefit of taking advantage of some of the new (and evidently very useful) features of that version to noticeably abridge the required set-up. So many thanks to Lori for sharing this knowledge and also congratulations on an excellent solution to a particularly complex challenge!

This is the fourth in a series of discussions on the techniques available for extracting numbers from an alphanumeric string.

In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In the second instalment (here) I looked at extracting consecutive numbers which appear at the end of the string, e.g. 123ABC456.

In the third instalment (here) I looked at extracting all numbers from a string where each of those numbers was to be returned to a separate cell. For example, given the string 81;8.75>@5279@4.=45>A?A; we extracted 81, 8.75, 5279, 4 and 45 into individual cells.

In this post I will look at a technique for extracting all numbers from a string, but where those numbers are to be returned as a single number in a single cell.

At the time of writing (Saturday morning, UK time; apologies if anyone has submitted something after that date), two correct solutions received (or three if you count non-Excel-based ones: as he has done for most of the recent challenges, Isai Alvarado produced a solution applicable to Google Sheets, which, as usual, I am unable to verify! So I’m taking your word for it that it’s perfectly correct, Isai! 🙂 ).

The two correct entries came courtesy of Snakehips, who gave a rather lengthy but perfectly correct solution, and John Jairo V, who improved upon his earlier attempt by producing a solution which, in essence, used a similar approach to Snakehips’ but which made use of some very nice technique involving MMULT to considerably abbreviate the required construction. Great work, John!

This is the second in a series of discussions on the techniques available for extracting numbers from an alphanumeric string. In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In this post I will concentrate on techniques for extracting numbers from a string where:

The numbers are consecutive

The consecutive string of numbers is found at the very end of the string

The desired result is to have those consecutive numbers returned to a single cell

As previously, for each of the given solutions, we need to test its soundness in two separate cases: firstly, where there are no numbers elsewhere in the string, e.g. ABC456 and secondly, where there are some numbers elsewhere in the string, either at the start, e.g. 123ABC456, or in the middle, e.g. ABC123DEF456.

This is the first in a series of discussions on the techniques available for extracting numbers from an alphanumeric string. Since we often have many different solutions at our disposable for such tasks, I will attempt to present what I feel are the principal candidates and, for each of these set-ups, discuss the merits and potential drawbacks inherent in each.

In the next instalment in this series I shall look at extracting consecutive numbers which appear at the end of the string, e.g. ABC123. In later posts I will deal with cases in which the desired numbers to be extracted are interspersed within the string in groups of one or more, e.g. ABC12DE345-FG6H789, in which case we may be interested in extracting either the number 123456789 into a single cell or each of 12, 345, 6 and 789 into four separate cells.

I shall also consider in future posts cases in which there may be several numbers within a string, though from which we wish to extract perhaps only one (or more) of these numbers, and for which our choice of extraction is based upon one or more criteria. For example, given a string of the form X12-X34-X56-X78-X90 we may wish to develop a technique which extracts the number immediately preceding the fourth occurrence of a hyphen within that string.

This one turns out to be a good deal more complex than it at first appears, and so perhaps not surprisingly no correct results were received..

GreasySpot at first thought that Advanced Filter would be a viable solution, but quickly realised that it wasn’t actually appropriate here. Besides, as I mentioned, the idea of this (and of all these challenges in fact) is to try to achieve the results using worksheet formulas alone.