Search code examples
excelnumbersexcel-2007highlightconditional-formatting

Excel Conditional Formating to find numbers a cell with text

I have looked and looked but not found a suitable answer. I have a list of movies. Some with the year of release, some without.

Is there a conditional formatting way to highlight the cells that contain numbers. Typically four digit numbers. The numbers are always in different locations in the name and are never at the beginning or the end. They are also never by themselves. I found a couple of examples that don't work because "You may not use unions, intersections, or array constants for Conditional Formatting Criteria." What ever that means.

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

I would expect the second, third, fourth, sixth, seventh and eighth to be highlighted. I would be a bonus if it would only find the years and ignore the single digits but if not, that's OK too.

Two other points, I am using Excel 2007 because it's paid for and I am not getting a subscription for something I own and two, no visual basic or macros please. I don't understand that stuff and really don't want to.

Google and didn't find anything that suited me and typed into these forms.

I'm hoping someone will have a formula that will work in conditional formatting to highlight cells that have 4 numeric digits somewhere in the cell.

Solution

  • I think this should work in Excel 2007:

    =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}

    The formula checks each group of four (4) characters, and if any of them create a number, it will be positive (so numbers of 4 digits or more).

    If you need to actually make sure it is four digits and not more than four, and that the four digits make up a year between certain dates, you can add complexity to the formula.

    The substitution of ~ for space and the appending of a ~ at the end are because Excel will interpret <space><digit> as a number; and also compensate for how MID works.

    enter image description here