CODEWITHSUNDEEP
excelexcel-formulaconditional-statements
Shak
Shak

Reputation: 133

Highlight cells that don't have number

I was looking to create a true/false formula that is able to view a cell and determine whether it has a number in it (data type is 'Text').

E.g. Cells

enter image description here

Where as the desired output is:

enter image description here

Note: special characters like "_" or "-" are ignored.

Thanks for any help!

Upvotes: 1

Views: 834

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

This is what I have tried, and come up with,

FORMULA_SOLUTION

• Formula used in cell B1

=MAX(IFERROR(FIND(CHAR(ROW($48:$57)),A1),""))>0

Basically this part FIND(CHAR(ROW($48:$57)),A1) check whether there is a number or not, if it finds it, then we can use either MAX() or MIN() or COUNT() function to get the respective values. And then using a Boolean to check whether TRUE or FALSE

Or, You may use the COUNT() Function as well,

• Formula used in cell E1

=COUNT(FIND(CHAR(ROW($48:$57)),A1))>0

FORMULA_SOLUTION

Use any one of the above formulas in Conditional Formatting Rules --> Use a formula to determine which cells to format. Shall highlight the one has a number along with it.

Upvotes: 1

Related Questions