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:

Excel: Check if Cell Contains One of Several Values
Excel: Check if Cell Contains Date
Excel: Calculate Average If Cell Contains Text

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *