Advanced Formula Challenge #12: Results and Discussion 5

Last week I set readers the challenge which can be found here.

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

Shortest Formula Challenge #2: Results and Discussion 5

Last week I set readers the challenge which can be found here.

5 correct solutions received, courtesy of John Jairo V, GreasySpot, Bill Szysz, James and ChrisBM (who actually missed off a final parenthesis in his formula, though I will be lenient here!). So well done to all!

As to whose was the shortest, excluding the offering from Isai Alvarado, who beat everyone with his 51-character (excluding the equals sign) Google Sheets construction (well done Isai!), that accolade is shared by John and Bill, both of whose solutions came in at 56 characters, which is quite a remarkable coincidence when you consider that each used a completely different construction! So congratulations to John and Bill!

More…

Extracting numbers from a string 4: All numbers to a single cell 2

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.

More…

Extracting numbers from a string 1: Consecutive numbers at start 8

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.

More…