Excel Conditional Formating to find numbers a cell with text

Question

I looked and looked but couldn't come up with a satisfactory response. I've got a list of films. Some have the year of release, while others do not.

Is there a way to highlight the cells that contain numbers using conditional formatting. usually have four digits. The numerals are never at the start or end of the name; instead, they are always scattered throughout. Additionally, they are never left alone. Because "You may not utilise unions, intersections, or array constants for Conditional Formatting Criteria," I discovered a few examples that don't function. whatever that might imply.

Here is a sample list:

Airborne
Airplane II - The Sequel 1982
Alien 3 1992
Alien 1979
Alien Contamination
Alien Covenant 2017
Alien Resurrection 1997
Bad Santa 2

The second, third, fourth, sixth, seventh, and eighth would be what I would anticipate being featured. If it could only find the years and ignore the single digits, that would be ideal, but it's also fine if it can't.

Please refrain from utilising visual basic or macros. I am using Excel 2007 because it was purchased; I am not receiving a subscription for something I already own. I truly don't want to grasp that things because I don't.

Answer 1

Try this:

=OR(ISNUMBER(-MID(SUBSTITUTE(A1," ","~")&"~",seq,4)))

where seq is a defined name that refers to:

=ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,255))

seq merely returns an array of numbers {1..255}