EXCEL search and return

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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),""))
 
Upvote 0
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
AB
1X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H,C]) (Rooney 1984 [5,6,11,S,M,H,C])2013
2X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H])(Rooney 1984 [5,6,11,S,M,H])2013
3X (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!
6X (Burns 1982 [1,2,3,4,S,M,H])(Rooney 1984 [5,6,11,S,M,H])1984
7X (BFP 2013) (Burns 1982 [2,4,M,H])(Rooney 1984 [5,6,M,H])2013
Sheet
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,222,039
Messages
6,163,552
Members
451,843
Latest member
vitto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top