How to count number of cells with text or number in Excel?
When working with a worksheet that contains a mixture of text and numbers, you may need to determine how many cells contain text and how many hold numerical values. This is a common requirement, whether you are analyzing survey responses, processing lists of data, or preparing reports. Excel offers multiple practical methods to help you count cells with specific types of content efficiently. In the following tutorial, we will guide you through various approaches, explaining their suitable scenarios, detailed steps, and additional tips to avoid potential errors.
Count number of cells with text using formula
Count number of cells with numbers using formula
Count cells with text or numbers using VBA code (macro)
Count number of cells with text using formula
To count how many cells within a specific range contain text values, you can make use of an Excel formula that excludes numerical cells. This method is especially handy for quick statistics and works well in a wide variety of worksheets where you want to separate textual information from numbers.
1. Begin by selecting a blank cell where you wish to display the count result.
2. Copy and paste the following formula into the Formula Bar:
=COUNTA(A1:D15)-COUNT(A1:D15)After pressing Enter, you will see the total count of cells containing text appears in the selected cell.
Note: Make sure to adjust the cell range (A1:D15 in the example above) according to the area in your worksheet you wish to count. This formula counts all non-empty cells and subtracts the number of cells containing numbers, leaving only the count of cells with text. Empty cells and cells containing errors or formula outputs may be included in COUNTA, so review your selection for best results.
Tip: If your range includes cells with formulas returning empty strings (""), these will be counted by COUNTA as text. Consider verifying your data if you require an exact count of manually entered text values.
Count number of cells with numbers using formula
Once you know how many text cells you have, you may also need to count the number of cells containing numbers. This method is straightforward and suitable for datasets where you need to know how many responses are numerical, such as survey scores, quantities, or any other number-based information.
1. Select a blank cell to display the number counting result.
2. Copy and paste the formula below into the Formula Bar:
=COUNT(A1:D15)Then press Enter to display the result in your chosen cell.
Note: Likewise, modify the cell range (A1:D15 in the example) to fit your actual worksheet area. The COUNT function only tallies cells with numeric values and ignores any kinds of text or blank cells. If your numbers are formatted as text, this formula will not count them, so ensure the cells are properly formatted for accurate results.
Tip: For ranges including dates (since Excel stores dates as serial numbers), COUNT will include them as numbers in the result.
Count cells with text or numbers using VBA code (macro)
For more advanced needs or when working with large datasets, using a VBA macro can greatly simplify the process of counting cells with text or numbers. This approach is particularly useful if you want to repeat the operation across different ranges or automate your workflow. It also allows you to customize your counting criteria as needed.
1. On the Excel ribbon, click Developer Tools (if you don’t see this tab, enable it from Excel Options), then choose Visual Basic to open the Microsoft Visual Basic for Applications window.
2. In the VBA window, click Insert > Module and paste the following code into the module:
Sub CountCellsTextOrNumbers()
Dim WorkRng As Range
Dim xTitleId As String
Dim cell As Range
Dim TextCount As Long
Dim NumberCount As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to count:", xTitleId, WorkRng.Address, Type:=8)
TextCount = 0
NumberCount = 0
For Each cell In WorkRng
If VBA.IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
NumberCount = NumberCount + 1
ElseIf VBA.TypeName(cell.Value) = "String" And Not IsEmpty(cell.Value) Then
TextCount = TextCount + 1
End If
Next
MsgBox "Cells with numbers: " & NumberCount & vbCrLf & "Cells with text: " & TextCount, vbInformation, xTitleId
End Sub3. To execute the code, click the 
Run button (or press F5). A dialog box will prompt you to select the range you want to count.
This macro will display the count of cells containing text and numbers separately in a pop-up message box. The code ignores blank cells and counts only cells with content.
Related articles:
- How to count cell numbers between two values or dates in Excel?
- How to count cells with specific text in Excel?
- How to count number or “Yes” or “No” answer in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in