How to count how many times a number appears in a string?

Logan Burke

For example, if a cell contains the number 4500 and I want to count how many times 0 appears, I'd expect the formula to return a value of 2. Every formula I've tried simply checks if 0 is in the string at all and returns 1 for true and 0 for false.

Paul H

Try this

=LEN(String@row) - LEN(SUBSTITUTE(String@row, "0", ""))

Kelly Moore

Hey @Logan Burke

Building on Paul's formula above, if the number(s) you are searching for are more than one digit long you might need this. The x represents the number(s) you are searching for.

(LEN(String@row) - LEN(SUBSTITUTE(String@row, "x", ""))) / LEN("x")

cheers

Kelly