Excel Formulas to Count Numbers Stored as Text

Struggling with Excel ignoring numbers stored as text during critical financial audits is a frustrating, common hurdle. When aggregating portfolio data from standard funding sources-such as federal grants or private endowments-mismatched formatting often skews your totals. Masterfully resolving this inconsistency grants analysts absolute data integrity, ensuring no asset is unaccounted for.

Stipulation: This count method assumes your dataset contains non-blank, numeric text strings to prevent formula errors.

In practice, this is vital for NSF or USDA award tracking sheets where financial codes often import as text. Below, we provide the precise formula to instantly convert and count these values.

In Excel, data integrity is everything. However, database exports, CSV imports, and manual entries often introduce a common headache: numbers formatted as text. These "text-numbers" look exactly like standard digits, but Excel treats them as text strings.

This formatting mismatch causes standard formulas like COUNT, SUM, and AVERAGE to completely ignore these values. If you have a column containing a mix of true numbers, numbers expressed as text, and standard text strings (like names or codes), counting them accurately requires a specialized approach.

This guide will walk you through the exact formulas and techniques to count numbers expressed as text in Excel, whether you want to count only those text-numbers or include them in a total numeric count.

The Root of the Problem: Why Standard Functions Fail

To understand the solution, we must first understand how Excel categorizes data. Excel uses three primary data types: Numbers, Text, and Logical values (TRUE/FALSE).

• COUNT function: This function only counts cells containing native numerical data. It completely ignores text, even if that text consists of digits like "120".
• COUNTA function: This function counts any non-empty cell. It does not differentiate between the number 120, the text string "120", or the word "Apple".

Consider the following sample dataset in range A2:A7:

If you run =COUNT(A2:A7), Excel returns 2 (counting only A2 and A5). If you run =COUNTA(A2:A7), Excel returns 5 (counting everything except the blank cell). Let's look at how we target only those pesky text-numbers.

Formula 1: Count ONLY Numbers Expressed as Text

If your goal is to identify and count only the cells that contain numeric digits formatted as text (such as cells A3 and A6 in our table), you can combine the power of SUMPRODUCT, ISTEXT, and mathematical conversion functions.

Use the following formula:

How This Formula Works Step-by-Step

The beauty of SUMPRODUCT is its ability to process arrays without requiring you to press Ctrl+Shift+Enter (in older Excel versions). Let's dissect the inner mechanics of this formula:

1. ISTEXT(A2:A7): This evaluates each cell in the range to check if it is formatted as text.
   It returns an array of logical values: {FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
2. VALUE(A2:A7): This function attempts to convert text strings into actual numbers.
   • For '250, it returns the number 250.
   • For the word "Active", it cannot convert it, so it returns a #VALUE! error.
   • For true numbers and blank cells, it converts them or handles them accordingly.
3. IFERROR(..., ""): This intercepts the #VALUE! errors generated by non-numeric text like "Active" and converts them into empty strings "". This prevents the entire formula from breaking and returning an error.
4. ISNUMBER(...): This checks whether the outputs from the step above are numbers.
   It returns an array: {TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}. (Note: True numbers and text-numbers both evaluate to TRUE here because VALUE successfully converted both).
5. The Multiplication (*): Excel multiplies the two arrays together. In math operations, TRUE behaves as 1 and FALSE behaves as 0.
   

     ISTEXT Array:   {FALSE,  TRUE,  TRUE, FALSE,  TRUE, FALSE} (0, 1, 1, 0, 1, 0)
     *
     ISNUMBER Array: { TRUE,  TRUE, FALSE,  TRUE,  TRUE, FALSE} (1, 1, 0, 1, 1, 0)
     -------------------------------------------------------------
     Result Array:   {    0,     1,     0,     0,     1,     0}
         

6. SUMPRODUCT: Finally, SUMPRODUCT sums the values in the result array: 0 + 1 + 0 + 0 + 1 + 0 = 2.

Formula 2: Count ALL Numbers (Standard and Text-Formatted)

What if you want to find the total count of all numbers in a range, regardless of whether they are correctly formatted as numbers or incorrectly formatted as text? In our sample table, this would mean counting A2, A3, A5, and A6 (total count of 4).

To achieve this, use this array-based formula:

How This Formula Works

• VALUE(A2:A7) attempts to normalize everything into a number. Genuine numbers remain numbers; text-numbers are successfully converted to numbers; genuine text strings convert to errors.
• IFERROR(..., "") cleans up the errors, leaving us with real numbers and empty strings.
• ISNUMBER(...) returns TRUE for both native numbers and successfully converted text-numbers.
• The Double Unary (--): Since SUMPRODUCT requires numbers to calculate a sum, we use the double negative (unary operator) to coerce the TRUE and FALSE logical values into 1s and 0s.

Alternative Method: Using a Helper Column (Simplest Approach)

If complex array formulas make you uneasy, you can easily solve this problem by creating a helper column. This is often the best practice for collaborative worksheets where other users need to understand the logic quickly.

Assuming your data is in Column A, insert a new column (Column B) and enter this formula in cell B2, then drag it down:

This formula checks two conditions: if the cell is text, and if that text can be mathematically converted to a number. If both conditions are met, it returns a 1; otherwise, it returns a 0. To find your total count, simply sum Column B using a standard =SUM(B2:B7) formula.

How to Permanently Convert Text-Numbers Back to Real Numbers

While formulas are great for reporting, the presence of text-formatted numbers often points to underlying data quality issues. If you want to clean up your sheet and convert these values back to standard numbers, Excel offers a few incredibly fast built-in tools:

Method A: The Error Indicator Dropdown

1. Select the cells showing a small green triangle in the top-left corner.
2. Click the yellow warning icon that appears next to the selection.
3. Select Convert to Number from the dropdown menu.

Method B: Text to Columns Wizard (Best for Large Datasets)

1. Select the entire column of data you wish to convert.
2. Go to the Data tab on the Ribbon.
3. Click Text to Columns.
4. In the wizard that pops up, simply click Finish immediately without changing any settings. Excel will automatically re-evaluate the data type of every cell in that column, converting text-numbers back to standard numbers instantly.

Summary Cheat Sheet

Depending on your exact goal, choose the formula that matches your scenario: