EXCEL search and return

[Maggie Barr]

I am trying to find a way for excel to search through a column of references within cells separated in the cell by () and return only the most recent year of publication. I would like not to have to do this manually by going through 1000+ cells for 15 columns. Is there a way to do this?

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

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

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

#N/A

#N/A

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

X (BFP 2013) (Burns 1982 [2,4,M,H])(Rooney 1984 [5,6,M,H])

<tbody>
</tbody>

For example, in this case I would like to have the formula return 2013 for the first three rows, N/A would be fine for row four and five because there is no data , and return 1984 for six and 2013 for seven because these are the most recent years of publication. Perhaps it is impossible, but thought I would give it a shot and see if anyone knew how to approach this resulting in less manual effort.
Thank you for your time,
Maggie

 

[Maggie Barr]

To add detail to my above post, I have a list of 18 years that I can search for within the cells. Not sure if a nested If function can pick out a year, if it occurs, within a cell and return the highest value of : 1901, 1927, 1938, 1978, 1982, 1983, 1984, 1985, 1990, 1994, 1996, 1998, 2004, 2005, 2007, 2010, 2012, 2013.
I would love any suggestions.
Thank you,
Maggie

 

[István Hirsch]

Put this formula in the next column with Shift-Ctrl-Enter (not just Enter), then drag it down and right as needed:

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

 

[Maggie Barr]

I tried it and it only returns a zero. Any further input?
I really appreciate your help!
Mags

 

[István Hirsch]

I tried it and it only returns a zero. Any further input?
I really appreciate your help!
Mags

Which excel version are you using?

For me (with excel 2007) it works as required:

Excel Workbook
	A	B
1	X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H,C]) (Rooney 1984 [5,6,11,S,M,H,C])	2013
2	X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H])(Rooney 1984 [5,6,11,S,M,H])	2013
3	X (BFP 2013) (Lortie et al. 1996) (Burns 1982 [1,2,3,4,S,M,C])(Rooney 1984 [5,6,11,S,M,H])	2013
4	#N/A	#VALUE!
5	#N/A	#VALUE!
6	X (Burns 1982 [1,2,3,4,S,M,H])(Rooney 1984 [5,6,11,S,M,H])	1984
7	X (BFP 2013) (Burns 1982 [2,4,M,H])(Rooney 1984 [5,6,M,H])	2013
Sheet

 

[Maggie Barr]

Istvan,
Thank you so much for this. I did get it to work. Other than the formula reading other numbers in the cell that are not years (plant specimen numbers like GHM0627201204), which I can deal with by manually looking at those when they turn up, it is working. Not totally sure what I did wrong, other than having been at it too long and being frazzled. I can't thank you enough for this! This is truly fabulous!
With much gratitude,
Mags

 

[István Hirsch]

You are welcome. Please let me know if you are going to extend the procedure to additional strings (more complex than the ones in post #1) where this formula needs modifications.

 

[Maggie Barr]

Istvan,
Thank you. I just modified the formula (cell reference) for other columns as needed to calculate and it worked perfect! I can't tell you how much time and hassle this saved me. I still have glitches to work out because of other details in the data (I didn't set up the original database), but it is minimal compared to reading the contents of over 15,000 cells individually or splitting columns up to multiple columns and running multiple searches and max formulas etc.!
Thank you again for your help and for the offer of more!
Best wishes,
Maggie

 

[Maggie Barr]

You are welcome. Please let me know if you are going to extend the procedure to additional strings (more complex than the ones in post #1) where this formula needs modifications.

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

 

[István Hirsch]

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.