Extracting numbers from a string 2: Consecutive numbers at end 3

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.

More…

Advanced Formula Challenge #8: Facetious? Moi? 25

The challenge this week is as follows: given a single paragraph of text in A1, which may or may not contain punctuation, a single formula in B1 to identify the number of words within that text which contain all five vowels of the English alphabet precisely once each and in an order of appearance, from left to right, of a, e, i, o, u.

For the below example the result would be 8, as highlighted in red.

Advanced Formula Challenge 8 Facetious Moi v2

Edit: I have now amended the text to make it clearer that certain words are not to be considered in the count: those in black, for example, do not meet the requirements as outlined above.

The workbook can be downloaded here.

Solution next week. Best of luck!

Shortest Formula Challenge #1: Results and Discussion 2

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

Two correct answers received (three if you count Snakehips‘ improvement) from GreasySpot and Bill Szysz, of which Bill’s was the shorter of the two (74 characters compared to 249, excluding the equals sign).

Snakehips then came along and improved this to a mere 70 characters simply by making the reference to the required range relative. (I can imagine Bill is now kicking himself for using absolute references in a shortest-formula challenge!)

Anyway, between the two of them they managed to come up with what is indeed (at least, that I know of) the shortest possible solution to this problem, and that solution is:

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…

Collating from multiple sheets based on conditions 28

Some of us may be familiar with the standard technique using INDEX, SMALL, etc. which, given a single-column or single-row array, we can use to return a list of only those values which satisfy one or more criteria of our choosing.

In a previous post (see here) I outlined a method which, given a range consisting of more than one column, returned a single column consisting of all non-blank entries from that range. It can easily be verified that the single condition within this formula (i.e. that the entry be non-blank) can be extended to multiple criteria and so, effectively, we now have at our disposable the means with which to generate single-column lists from both one- and two-dimensional arrays.

But can we go one further yet again? “Three-dimensional” is the collective term often applied to those formulas in Excel which are capable of operating over not just single columns or rows, nor yet ranges consisting of multiple columns or rows (two-dimensional), but which also function effectively over multiple worksheets.

More…

Advanced Formula Challenge #7: Results and Discussion 1

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

This is a trickier problem than it at first appears, and indeed there are several pitfalls which prevent us from using more “standard” techniques to arrive at a solution.

Perhaps the two main (hidden) obstacles, which were not immediately obvious from the examples I gave, are, firstly, the fact that we are prevented from using a construction involving a SEARCH-approach (e.g. by locating occurrences of each substring of the four types *????*, †????*, *????† and †????†, as John Jairo V attempted), since this of course presumes that there is only one occurrence of each of those substring types within our string, a presumption which cannot be made.

More…

Converting from base 10 to base x Reply

This is a formula-based solution which, given a positive number in base 10, converts that number into its equivalent form for another, given positive base.

Convert from base 10 to base x

So, given a base-10 value to be converted in A2, and the base to which we wish to convert this value in B2, the required output is generated in C2. Cell D2 contains a “back-check” which re-converts the value in C2 to base 10.

The formula in C2 is:

=SUMPRODUCT(MOD(FLOOR(A2/B2^(ROW(INDIRECT("1:20"))-1),1),B2)*10^(ROW(INDIRECT("1:20"))-1))

More…

1/17 and other pandigitals 9

A slightly light-hearted post this, as you may have guessed from the title, though readers might find it moderately interesting, and hopefully some may even contribute to my rather esoteric collection of pandigital numbers in Excel.

This began as a result of seeing (I don’t recall where now, unfortunately) an alternative version of the tried-and-tested construction for returning a number from the end of a mixed string. For example, given the following in A1:

ABCDEF123456

the now-ubiquitous solution:

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

will correctly return 123456.

More…

Non-Array TRANSPOSE 4

We sometimes look for non-array (i.e. non-CSE) versions of constructions which would normally require array-entry. Our reasons for doing so may be varied:

1) We may feel that it improves spreadsheet performance (sometimes true, sometimes not)

2) We perhaps have a dislike for having to use the required keystroke combination necessary for committing array formulas

3) We may simply be interested from a theoretical point of view

More…

Advanced Formula Challenge #6: Results and Discussion Reply

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

This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.

It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.

Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).

More…

List of unique entries from column of space-separated strings 5

Given the list below in A1:A10, we may wish to create a list of unique, single words from that list, as per column B here.

Unique From Space Separated

We can do this with the following set-up: More…

Advanced Formula Challenge #6: Occurs once, non-contiguous range 9

The challenge this week is as follows: given a non-contiguous range, made up of an arbitrary number of single-column ranges, the values in each of which being either a numeric, text or null string, to generate a list, in numerical order and beginning in A2, of all numbers which occur precisely once within that range.

For example, in the below:

Occur Once Non-Contiguous Ranges

More…

COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges 107

In this post I would like to clear up what appears to me to be a rather widespread misunderstanding of how COUNTIFS/SUMIFS operate, in particular when we pass arrays consisting of more than one element as the Criteria to one or even two of the Criteria_Ranges.

This latter technique is used when the criteria in question are to be considered as “OR” criteria, which is not to be confused with cases where we wish the criteria passed to be calculated rather as “AND” critieria.

For example, given the following data:

More…

Advanced Formula Challenge #5: Results and Discussion 5

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

This is a reasonably complex problem, and certainly so if we want to present a solution which is relatively concise. However, despite its complexity (and arguably lack of practical use), the solution demonstrates some important techniques for working with strings, and so is not without merit.

The required set-up is as follows:

More…

IFERROR techniques for excluding certain values from results 6

We are often in a position where we wish to exclude certain values from an array of results before passing that array to another function.

For example, a common, practical situation is that of finding the minimum value from a range whilst excluding zeroes. This can be done in several ways, for example using an array formula:

=MIN(IF(A1:A10<>0,A1:A10))

or, if we have Excel 2010 or later, using AGGREGATE:

=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)

More…

Advanced Formula Challenge #5: Threes, Fives and Sevens Reply

The challenge this week is as follows: given an alphanumeric string of arbitrary length in A1, derive a single formula to return the number of numbers within that string which are divisible by either 3, 5 or 7.

By “divisible” here I mean of course that there is no remainder after division.

And by “numbers within that string” I mean all consecutive substrings of any length within that string which may be interpreted as a number. (It can also safely be assumed that there are no alphanumeric combinations within the string in A1 which would be interpreted by Excel as numeric, e.g. JAN01.)

For example, the string:

XX30X5XXX42XX771

contains, by this definition, 13 numbers: 3, 0, 30, 5, 4, 2, 42, 7, 7, 1, 77, 71 and 771.

More…

Advanced Formula Challenge #4: Results and Discussion Reply

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

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.

So how can we achieve our desired results?

More…

Which numbers add up to total? 28

Sometimes we are in a situation where we have a target figure plus a series of values and we want to know which, if any, combination of those values has a sum which is equal to the target.

This can be done as follows:

Which numbers add up to total

Edit: this post has now been revised here to account for multiple returns, should that be a requirement.

Using the above set-up, with our target value in A2 and our (in this case 9) values in C1:K1, we will place formulas in C2:K2 which will contain an “X” if the value in the row above forms part of our solution.

More…