How to have a Conditional Formatting with variable digits

Question

I wish to create a conditional formatting rule to highlight any cell that has four digits together - like a Year but not a date format;
so to highlight the cell with "2026 Winter Olympics" where the year could be any year (not just 2026).

Any help will be appreciated
Thanks

Answer 1

Hi,

This conditional formatting formula works

=NOT(ISERROR(REGEXEXTRACT(A2,"\d{4}")))

Hope this helps.

Answer 2

any cell that has four digits together - like a Year

For the poster, you have an excellent solution, but functions like NOT, and ISERROR would not be necessary if looking for 4 digit numbers as in column C, unlike that of column A.

Answer 3

Hello DeanH,

Thank you for posting your question in the Microsoft Q&A forum!

I understand that you are having issue with conditional formatting in Excel. I truly understand how you feel. Let me assist you go through this situation.

With your situation, I suggest you try this:

1. Try 1: Highlights any cell that contains 4 digits anywhere in the text

• Select the cells you want to format
• Go to Home > Conditional Formatting > New Rule
• Choose Use a formula to determine which cells to format
• Enter this formula: =REGEXMATCH(A1,"\d{4}")
• Click Format…, choose your style
• Click OK

Works for: 2026 Winter Olympics. No helper cells

2. Try 2:

• Select the cells you want to format
• Go to Home > Conditional Formatting > New Rule
• Choose Use a formula
• Enter this formula: =SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$99),4)))>0
• Click Format…
• Click OK

Finds any 4‑digit number inside text. Compatible with older Excel

Important:

• A1 must be the top‑left cell of the selected range
• Excel does NOT support regex in SEARCH, which is why your original formula failed

I hope this will help with your situation. Please feel free to reach back if you have further update or more questions.

Best Regards,

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

To create a conditional formatting rule in Excel that highlights any cell containing a four-digit number (like a year), you can use a formula-based approach. Here’s how you can set it up:

1. Select the range of cells you want to apply the formatting to.
2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
3. Choose Use a formula to determine which cells to format.
4. In the formula box, enter the following formula:

      =AND(ISNUMBER(VALUE(MID(A1,SEARCH("[0-9][0-9][0-9][0-9]",A1),4))), LEN(A1) > 4)
   

   Replace A1 with the first cell in your selected range.
5. Click on the Format button to choose the formatting options (like fill color or font style) you want to apply when the condition is met.
6. Click OK to apply the rule.

This formula checks for a four-digit sequence within the text and highlights the cell if it finds one. The SEARCH function looks for a four-digit number, and ISNUMBER checks if the result is a number.

---

References:

• Create conditional formatting rules
• Working with conditional formatting
• Conditional formatting samples
• How to control and understand settings in the Format Cells dialog box in Excel