Pull Data from Text Field in Excel

mmmcdee used Ask the Experts™
I am trying to pull YEAR data from a text within a Cell that contains two ways of displaying YEAR - Example:  2010 or 1997 or 97 or 1999 or 99.  I have attached my Excel doc using lots of TRIM/IF/FIND parameters.  I was able to segregate the fields to their proper YEAR column.  This is not very efficient and of course not easy.

All I really need is a way to PULL the YEAR data from a specific column containing subject text.  When YEAR data is found like 2000 or 2011 or 97 or 98,  Excel copies that year to a column and corrects the two digit year and makes it a four digit year.  The data (text) I am pulling from will contain data from 1997 to 2014 - years are subject to change as time goes by of course.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Please see attached. It's not pretty and it's by no means bullet-proof, but it found all of the years in your sample data. (There are a few differences (highlighted in red) between your results and mine, but mine look correct.)

A few notes...
(1) It's taking the first numeric value in the first 5 words. It could be expanded to handle more words by modifying two formulas and adding a column per word.
(2) The words are delimited by spaces, single-quotes, ampersands and tildes. Expanding it to include other special characters (perhaps exclamation mark?) is just a matter of adding more SUBSTITUTES() in column CD.
(3) Any values less than or equal to the last two digits of the current year have 2000 added to them. Any other values less than 100 have 1900 added.
(4) The formulas in columns CF to CI are array-entered (CTRL-SHIFT-ENTER). Let me know if you need any assistance with this.



Thanks Brian - I am testing now


Thanks Brian - I was traveling and did not get to close out the accepted answer.  Sorry for the delay -
No problem. Thanks, mmmcdee.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial