How to count numbers in a specific cell when formulas aren't recognizing them as numbers?

Sarah Stonestreet 20 Reputation points
2026-06-15T18:35:49.4933333+00:00

I have a report that counts the number of emails an email address has received from my company and would like to use a formula to aggregate that number per email address. However, with the way the report was formatted, Excel doesn't seem to be recognizing the numbers. I want to be able to create a column next to the number of emails column that aggregates the count.

Example:

Email Address Number of Emails Total Number
******@email.com [1:,1:,1:] ?

Each "1" represents an email, so, in this case, the email address has received three emails from us. But using the COUNT formula comes back with 0, and using the LEN formula comes back with 7, since it's counting all the characters. I've tried using LEN and SUBSTITUTE, and some COUNTIF formulas, but it doesn't seem to work.

This report has over a million addresses, so I can't change the format of the number of emails column easily.

Microsoft 365 and Office | Excel | For business | Windows
Answer accepted by question author
Marcin Policht 109.5K Reputation points MVP Volunteer Moderator
2026-06-15T19:31:12.3033333+00:00

If the format is always something like [1:,1:,1:], you can count how many times 1: appears in the cell. Assuming the “Number of Emails” value is in cell B2, use:

=(LEN(B2)-LEN(SUBSTITUTE(B2,"1:","")))/2

This should work because each occurrence of 1: is two characters long. The formula removes all instances of 1:, compares the original length to the shortened length, and divides by 2 to get the count.

For your example:

[1:,1:,1:]

the formula returns 3.

If there may be spaces or inconsistent formatting, a more reliable version is:

=IF(B2="","",(LEN(B2)-LEN(SUBSTITUTE(B2,"1:","")))/2)

Then fill the formula down your “Total Number” column. This avoids needing to reformat the original data, which should help with a dataset that large.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Dana D 100 Reputation points
    2026-06-16T13:00:04.3433333+00:00

    This report has over a million addresses, ( [1:,1:,1:] )

    Just to be different, with a million addresses, is a very long string really going to be useful?

    I might reconsider and just put a numerical count in directly.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.