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..

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))

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!