Count text within numbers

  • Thread starter Thread starter LiAD
  • Start date Start date
L

LiAD

Hi,

Is it possible to count how many times a letters appears in a text string.
Example

1 234 N8 0,4xF9

I would like a formula that gives me the result of 1 as N only appears once.

Is this possible?
Thanks
 
Try this:

=LEN(A1)-LEN(SUBSTITUTE(A1,"N",""))

Note that this is case sensitive. The above will not count lower case n.
 
one other way with array formula, to be confirm by Ctrl,Shift and Enter

=SUM(LEN(A2))-SUM(LEN(SUBSTITUTE(A2,"N","")))/LEN("N")

This is case sensitive, it will return 0 if you put a "n" instead of "N" in
your data.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis
 
If you want to count both upper and lower case letters as being the same:

1 2n4 N8 0,4xFN9n

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"N",""))

Count of N + n = 4
 
In what way do you intend SUM(LEN(A2)) to be different from LEN(A2) ?
In what way do you intend SUM(LEN(SUBSTITUTE(A2,"N",""))) to be different
from LEN(SUBSTITUTE(A2,"N","")) ?
Is there some use of the SUM function of which the rest of us are unaware?

Also, why the array formula? Where is the array? What would be different
if we just used enter instead of CSE?
 
Back
Top