Table of Contents
Excel is a powerful tool that allows users to perform various calculations and functions on data. One useful feature is the ability to return a value if a cell contains a number. This means that if a specific cell in a worksheet contains a number, Excel can be instructed to display a specific value or perform a certain action. This can be done using a combination of functions, such as IF and ISNUMBER, to check if a cell contains a number and then return a desired value or action. By using this feature, users can efficiently manage and manipulate data in their Excel spreadsheets.
Excel: If Cell Contains Number then Return Value
You can use the following formulas in Excel to return specific values if a cell contains a number:
Formula 1: Return Value if Cell is a Number
=IF(ISNUMBER(A2), A2, "")
This particular formula checks if cell A2 is a number. If it is a number, then the formula returns the number in cell A2. Otherwise, the formula returns a blank.
Formula 2: Return Value if Cell Contains Any Numbers
=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0, A2, "")
This particular formula checks if cell A2 contains any numbers. If it does, then the formula returns all of the contents of cell A2. Otherwise, the formula returns a blank.
The following examples show how to use each formula in practice with the following list of values in Excel:
Example 1: Return Value if Cell is a Number
We can type the following formula into cell B2 to return the value in cell A2 only if it is a number:
=IF(ISNUMBER(A2), A2, "")
We can then click and drag this formula down to each remaining cell in column B:
If the value in column A is a number, then column B simply returns the number.
Otherwise, column B returns a blank.
Note that you could also return other values if you’d like. For example, you could use the following formula to return “Yes” or “No” to indicate if the cell in column A is a number:
=IF(ISNUMBER(A2), "Yes", "No")
The following screenshot shows how to use this formula in practice:
Column B now displays “Yes” or “No” to indicate if the corresponding cell in column A is a number.
Example 2: Return Value if Cell Contains Any Numbers
We can type the following formula into cell B2 to return the value in cell A2 only if it contains a number:
=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0, A2, "")
We can then click and drag this formula down to each remaining cell in column B:
If the value in column A contains any numbers, then column B simply returns all of the contents of the cell.
Otherwise, column B returns a blank.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: