Search code examples
excelconditional-formatting

How to Format / Highlight cells that do not contain any number

For our address label printer we use an Excel file. Unfortunately in Holland many people forget to add their house number and I'm trying to fill them with red so our team knows they need to contact them to get their house number.

Here is an example: https://gyazo.com/89a0f89542b2256efdda45c7dec00ce4

As you see in cell D9. somebody forgot to enter a house number, so that cell should be filled red.

I googled a lot, but could not find the correct conditional formatting answer for it.

Solution

  • To find if string contains any number you can use this formula:

    =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},D1))>0

    It returns TRUE if there is a number and FALSE otherwise.

    Since you can't use arrays in conditional formatting you have to use this formula in "helper column", then you can use output (TRUE and FALSE) in conditional formatting.