Count text within numbers
 

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

Count text within numbers
 

Try this:

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

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

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
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

Count text within numbers
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

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

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

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
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

Count text within numbers
 

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?
--
David Biddulph

"francis" wrote in message
...
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

"LiAD" wrote:

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

Count text within numbers
 

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

"LiAD" wrote in message
...
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