Excel Conditional Formating to find numbers a cell with text-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

You can use the following formula in your conditional formatting:

=AND(ISNUMBER(SEARCH("????",A1)),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))<4)

This formula searches for a 4-digit number within the cell, and checks that the cell does not contain more than 3 spaces (since the year is not at the beginning or end of the cell).

To use this formula in your conditional formatting:

Select the cells that you want to apply the formatting to. In the Home tab, click on the Conditional Formatting button and select New Rule. In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format" and enter the formula above in the formula field. Click the Format button to choose the formatting that you want to apply to the cells, such as a background color or font color. Click OK to close the dialog boxes. This should highlight the cells that contain a 4-digit number.

Got this from ChatGPT no idea if it works lol.