We will use the following dataset to demonstrate how to use the ISNUMBER function for IF statements.
Example 1 – Check If a Cell Starts with a Number
Steps:
- Select cell C5 and insert the following formula.
=IF(ISNUMBER(VALUE(LEFT(B5,1))),"True","False")- Press Enter.
Formula Breakdown
- LEFT(B5,1) returns the first character from cell B5.
- ISNUMBER(VALUE(LEFT(B5,1)) checks if the first character of cell B5 is a number or not.
- IF(ISNUMBER(VALUE(LEFT(B5,1))),”True”,”False”) returns True if the first character is a number and False if the first character is not a number.
- AutoFill this formula to the rest of the cells in column C.
Read More: How to Use COUNTIF & ISNUMBER to Count Numbers in Excel
Example 2 – Check If a Cell Value Is a Number
Steps:
- Click on cell C5 and insert the following formula, then press Enter.
=IF(ISNUMBER(B5),"Yes","No")
Formula Breakdown
- ISNUMBER(B5) checks if the value of cell B5 is a number or not.
- IF(ISNUMBER(B5),”Yes”,”No”) returns Yes if cell B5 contains a number and returns No if the value of cell B5 is not a number.
- AutoFill this formula to the rest of the cell to get the outputs for all passwords.
Read More: Excel ISNUMBER Not Working
Example 3 – Look for a Text in a Cell
Steps:
- Select cell C5 and insert the formula given below.
=IF(ISNUMBER(SEARCH("john", B5)), "Yes", "No")- Hit Enter to get your desired output.
Formula Breakdown
- SEARCH(“john”, B5) finds the text “john” and returns its position.
- IF(ISNUMBER(SEARCH(“john”, B5)), “Yes”, “No”) returns Yes if the SEARCH function returns a number. Otherwise, it returns No.
- AutoFill this formula to the rest of the cells in column C.
Notes
- Don’t forget to give proper cell references or you won’t get the desired results.
- If you want to find out if a cell ends with a number, you can use the RIGHT function instead of the LEFT function in the first method.
Download the Practice Workbook
<< Go Back to Excel ISNUMBER Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
