- For ending
=COUNTIF(A1:A10,"*c")
c* is case insensitive. Hence, it will count cells starting with both c or C.
Suppose you want to find all cells starting with Excel.
=COUNTIF(A1:A10,"*excel")
15. Count No. of Cells Having Numbers Only
COUNT function counts only those cells which are having numbers.
Assuming your range is A1:A10, use following formula
=COUNT(A1:A10)
16. Count No. of Cells which are containing only Characters
Hence, if your cell is having a number 2.23, it will not be counted as it is a number.
Use below formula considering your range is A1:A
=COUNTIF(A1:A10,"*")
17. Number of Characters in a String without considering blanks
Say, you have a string like Vijay A. Verma and I need to know how many characters it has.
In this case, it has 12 including decimal and leaving blanks aside.
Use below formula for the same -
=LEN(SUBSTITUTE(A1," ",""))
18. Number of times a character appears in a string
Suppose you want to count the number of times, character “a” appears in a string
=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))
19. Count Non Numbers in a String
Suppose you have a string "abc123def45cd" and you want to count non numbers in this.
If your string is in A1, use following formula in A 1
