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 3: All numbers to individual cells 28

This is the third 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 this post I will demonstrate a technique for extracting all numbers from a string where:

  • The string in question consists of a mixture of numbers, letters and special characters
  • The numbers may appear anywhere within that string
  • Decimals within the string are to be returned as such
  • The desired result is to have all numbers returned to separate cells

More…

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…

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…

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…