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]

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),"")).....

Thank you for taking the time to explain. I did just buy some reference materials from the store of this website and did some searching on line to find other resources of explanation. When I learn new useful things in Excel I get all giddy like a kid on their birthday, it is all so exciting, and I think it is wonderful that this forum is here and there are such helpful friendly people. Thank you!
Best,
Maggie