Function Problems

How to Count Email Addresses and Mobile Numbers in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs a method to count the total number of member email addresses and mobile numbers in a spreadsheet while ensuring that the leading zeros in the phone numbers are not automatically deleted.

Product: Excel
Device & OS: not provided
Scenario: Compiling and analyzing contact lists that include both email addresses and mobile phone numbers.
Observed behavior: Excel automatically drops leading zeros from mobile numbers when they are entered. Furthermore, users need a way to accurately tally the total number of filled entries in these contact columns.

Before you start

Ensure your contact data is organized into clear columns, with email addresses in one column and mobile numbers in another, and check that there are no merged cells in your data range.

Solution 1Recommended

Use the COUNTA Function to Count Entries

The COUNTA function is ideal for this task because it counts all non-blank cells, making it perfect for tallying text-based entries like emails and text-formatted phone numbers.

Unlike the standard COUNT function, which only tallies numerical values, COUNTA calculates any cell that contains data, including letters, symbols, and text strings.

Select a destination cell

Click on a blank cell where you want the total count of your email addresses or mobile numbers to appear.

Enter the COUNTA formula

Type =COUNTA( followed by your data range. For example, if your emails are in column C from row 2 to 100, type =COUNTA(C2:C100).

Apply the formula

Press the Enter key. The cell will now display the total number of non-blank email addresses or mobile numbers in that specific range.

Solution 2

Format Cells as Text to Preserve Leading Zeros

By formatting the mobile number column as text before entering data, you prevent Excel's default behavior of removing mathematically insignificant leading zeros.

Efficient Data Management with WPS

Count and Format Contact Data Easily in WPS Spreadsheet

WPS Spreadsheet offers seamless functionality for managing contact lists. You can easily use the COUNTA function to tally emails and quickly format columns as text to preserve leading zeros in phone numbers.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your contact lists.
2. Apply the COUNTA function: Select an empty cell and enter =COUNTA(range) to quickly tally up the populated emails or numbers.
3. Adjust cell formats: Right-click your mobile numbers column, choose 'Format Cells', and select 'Text' to ensure zeros are not dropped.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Supports all standard spreadsheet formulas, including COUNTAIntuitive formatting options to keep leading zeros intactFree and lightweight Microsoft Office alternative

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel remove the leading zero from mobile numbers?

Excel treats numeric entries mathematically by default. Since a leading zero does not change the mathematical value of a number, Excel automatically drops it. To retain the zero, the cell must be formatted as text.

Can I use the COUNT function instead of COUNTA for email addresses?

No. The COUNT function only tallies cells containing numerical data. Because email addresses contain letters and symbols (like @), you must use COUNTA, which counts any cell that is not empty.

How do I fix mobile numbers that have already lost their leading zeros?

You can restore them using a custom format. Highlight the cells, right-click, select 'Format Cells', go to 'Custom', and type the correct amount of zeros (e.g., '0000000000') into the Type box. This will visually restore the leading zeros based on the digit count.

Excel & Data Problems