How to extract year from text in Excel/Google sheets-CodePudding

I want to extract the year from the Film Title Column using a formula.

CodePudding user response：

From your given sample data, you can use REGEXEXTRACT() function to extract years. Try-

=REGEXEXTRACT(B2,"\d ")

If your actual data have more numbers in each cell and want to extract years before word Directed then could Try-

=REGEXEXTRACT(B2, " \d  (?: Directed)?")

For array approach. Can use-

=ArrayFormula(REGEXEXTRACT(B2:B10, " \d  (?: Directed)?"))

CodePudding user response：

use:

=INDEX(IFNA(REGEXEXTRACT(B2:B11; "\d{4}")))