Ignore Text in Excel Formula

Sometimes when you use the regular addition (+), multiplication (*), division (/), or subtraction (-) operators in the Excel formula, you get the #VALUE error. This normally occurs because one or more cells in the data range contain values of the wrong data type, for example, text values instead of numeric values.

The following example shows the #VALUE error caused by text values in two cells.

Graphical user interface, application, table, Excel

Description automatically generated

The simplest way to correct this error is to replace the wrong data with the correct values. However, sometimes you may not have the correct values and may want the Excel formula to ignore the text values and work only with the numeric ones.

How to ignore text in Excel formula

I will show you three ways you can ignore text in Excel formula.

1. Use the functions such as SUM, AVERAGE, and PRODUCT rather than the regular addition (+), division (/), or multiplication (*) operators. These functions automatically ignore text values in the data range.

In the following example, the SUM function has automatically ignored the “Unfinished”, and “N/A” texts and summed up only the numeric values.

Graphical user interface, application, table, Excel

Description automatically generated

2. Use the SUBSTITUTE function to replace the text with an empty string. This will result in only numeric values remaining.

In the following example, the following formula has been used to get the correct total square feet of the office space:

=SUBSTITUTE(B2, "sq ft", "")+SUBSTITUTE(B3, "sq ft","")+SUBSTITUTE(B4,"sq ft","")+SUBSTITUTE(B5,"sq ft","")+SUBSTITUTE(B6,"sq ft","")
Application, table, Excel

Description automatically generated

3. Use the IFERROR function to ignore the text values and replace them with zeros.

Table

Description automatically generated

In the example above, the following formula has been used:

=SUM(IFERROR(B2*1,0),IFERROR(B3*1,0),IFERROR(B4*1,0),IFERROR(B5*1,0),IFERROR(B6*1,0))

Within the IFERROR function, multiplying by 1 does not alter the value but generates the #VALUE error if the value is text. The error is then replaced with a zero. If there is no error the values in the cells are used.