CODEWITHSUNDEEP
excelexcel-formula
skkakkar
skkakkar

Reputation: 2828

Formula logic for extracting specific cell data

For extracting First £ figure and not the second one from sample data

(24M UNLTD+INS 30GB £347+£30 S6)

Following array formula has been used in Stackoverflow questions. 

   {=MID(A1,FIND("£",A1),MIN(IF(ISERROR(MID(MID(A1,FIND("£",A1)+1,999),ROW($1:$999),1)+0),ROW($1:$999),999)))}

I attempted an analysis of the formula as represented in the image below. I am not able to grasp the logic of part

MIN(IF(ISERROR(MID(MID(A1,FIND("£",A1)+1,999),ROW($1:$999),1)+0),ROW($1:$999),999))

As to how it leads to a figure of 4. Request that this part of formula be elaborated to clarify the role of various constituents of this formula.

ste by step working on formula

Upvotes: 1

Views: 65

Answers (1)

user4039065
user4039065

Reputation:

Try the following as a standard (non-array) formula,

=--REPLACE(REPLACE(A2, 1, FIND("£", A2), ""), FIND("+", REPLACE(A2, 1, FIND("£", A2), "")), LEN(A2), "")

First the inside REPLACE(A2, 1, FIND("£", A2), "") erases everything up to the first £ symbol, then the same logic is applied to erase everything in that modified text from the first + to the end. The -- converts text-that-looks-like-a-number to an actual number.

The array formula you provided uses a more convoluted logic.

  1. FIND("£", A2) + 1 finds the starting point of the first number after the first £ symbol. e.g. The first £ is the 20th character so it returns 21.
  2. MID(A2, FIND("£",A2)+1, 999) extracts the text following that first £ symbol. The text might look like it starts with a number but it is text masquerading as a number. e.g. 347+£30 S6
  3. In an array formula, ROW($1:$999) processes as a sequence of numbers from 1 to 999, incrementing by 1 for each cycle of calculation.
  4. MID(MID(A1, FIND("£", A1) + 1, 999), ROW($1:$999), 1) + 0) returns an array of text values, each one 1 character long and 1 position deeper into the text than the previous one. e.g. 3, 4, 7, +, £, etc.
  5. +0 is used to try and convert each of these pieces of text to a number. The IFERROR function returns TRUE if a piece of text cannot be converted to a true number. The first one that cannot be turned into a true number is the 4th e.g. +
  6. The IF catches the TRUE on the fourth position and returns 4 from the second ROW($1:$999). It has returned 999 for positions 1, 2 and 3. e.g. 999, 999, 999, 4, 5, etc.
  7. The MIN catches the smallest of these numbers returned as an array. This is where the 4 comes from. e.g. 999, 999, 999, 4, 5, 999, ...

You can see this yourself by changing all of the 999's to 9 then using the Evaluate Formula command. The reason changing to 9 is important is so that the returned arrays of number look like 1, 2, 3, 4, 5, 6, 7, 8, 9 which does not obfuscate the results quite a badly as 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, ... 998, 999.

  evaluate_formula
      This shows the formula evaluated several steps into the process. Note the 4 being returned to the MIN function.

Upvotes: 3

Related Questions