Count text within numbers

[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

 

[T. Valko]

Try this:

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

Note that this is case sensitive. The above will not count lower case n.

 

[francis]

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

 

[T. Valko]

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

 

[David Biddulph]

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?

 

[Ashish Mathur]

Hi,

You can also try this array formula (Ctrl+Shift+Enter)

=SUM(1*(MID($D$13,ROW(INDIRECT("1:"&LEN(D13))),1)="N"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com