Istvan,

I hate to bother you with further questions, but could you please describe what each of the tasks in the formula you gave me do. I would like to think that I can learn and not just use the information I am provided, and this formula you wrote for me was GREAT and contains new things to me for sure. As well (OR) can you recommend a good book or online training source? I would like to think someday my literacy in excel would be to the point that I too could help others rather than just post questions and I am always open to advice on sources for learning. I am constantly going through very large excel data sets of various scientific nature and am always trying to find ways to extract what I need from them as they are not always designed in a user friendly fashion. Thank you again for your time and effort.

Best Wishes,

Maggie

Example:

X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H,C]) (Rooney 1984 [5,6,11,S,M,H,C])

=MAX(IFERROR(1*

**MID(A1,ROW(INDIRECT("1:"&LEN(A1))),4)**,""))

The task is to extract the latest year from the string above. To do this, excel has to recognize the numbers in that string and extract the highest.

As you can see, MID function (green) has 3 arguments separated by commas:

The first one is the cell (now A1) where we try to find the latest year;

The third argument is 4, which is the length of the substring just being under test (now constant);

The second argument in Mid function shows where we are going to take from the 4 character long substring to check if it is a number. So the part

**ROW(INDIRECT("1:"&LEN(A1)))**
in a formula entered with Ctrl-Shift-Enter does not do anything but produces the series of numbers from 1 to len(a1) that will be the starting point of the 4 character long substring being under check if it is a number:

1,2,4,5,6,7,8 ……len(a1)-1, len(a)

It means MID function produces the following series of substrings:

X (B

**>** (BF

**>**(BFP>BFP

**>**FP 2

**>**P 20

**>** 201

**>2013>**013)

**>** etc…..>

**1982**>…>

**1984>** …

The substrings above are text strings, which can be multiplied only if they represent a number, otherwise give error. So multiplying the strings by 1 checks if they can be converted into a number (are numbers) or not.

IFERROR function produces the first argument if there is not an error, the second argument (now „” that is an empty string) if there is. So IFERROR function sends only the three bold four-digit numbers to the MAX function, which chooses the highest from the three (2013).

As for studying Excel, after acquiring the bases from, say the Help file, basic books on Excel functions, or the net, when it comes to studying practical examples of use, it is a good idea to go through the functions of a given formula, starting from the „core” and see from function to function what each of them „does”, what their input and output is, how the end result is created. Placing the cursor over a function and clicking on fx button, a window of that function appears displaying the arguments, at the same time that part of the formula gets bold.

Also, it may be informative to put each function of a formula in a separate cell, and re-build the formula from those cells to help understand its working.