Extracting a date from a file name.

Hi All,

I have over a 1000 movie files in various file formats. Periodically I use a XLS to create a listing of the file names.
All of the file names include a date year in the YYYY format.
The files are not named consistently. Here a few examples:

Air.Force.One.1997.720p.BRrip.x264.YIFY
Along.Came.A.Spider.2001.Alex Cross Drama w. Morgan Freeman 122m
American Sniper (2014) DvD Scr Rip - X264 1080p
Apartment, The 1960.720p.BRRip.x264-x0r
Apollo.13.1995.720p.BluRay.x264.YIFY

What i'd like is a function that can extract the YYYY and place it in a cell next to the file name

I'm not sure if it helps but the date value is always going to be between 1925 and 2016.

I apologise for asking this question in two forums but i'm unsure which one would be best suited..

Formulas that use the ROW( ) function are vulnerable to new row insertions.

ROW($1925:$2016)

If you inserted a new row 1 that would change to:

ROW($1926:$2017)

And now you're missing any years 1925.

Using the INDIRECT function can account for that.

ROW(INDIRECT("1925:2016"))

New row insertions will have no impact on that syntax.

You're welcome!

Originally Posted by Jonmo1

So your formula is probably as good as it can get.

=LOOKUP(2,1/SEARCH(ROW($1925:$2016),A1),ROW($1925:$2016))

Aside from my previous point about the ROW function...

=LOOKUP(1E100,SEARCH(ROW($1925:$2016),A1),ROW($1925:$2016))