Determine Number of Characters in a Row

  • Thread starter Thread starter t2true
  • Start date Start date
T

t2true

I have a spreadsheet that contains network information. I am trying to
determine the number of times a specific letter appears in a row. Is
there a formula that will look for that letter, count the number of
times it appears in that row and then input it into a specific cell?

Thanks,
Ted
 
t2true said:
I have a spreadsheet that contains network information. I am trying to
determine the number of times a specific letter appears in a row. Is
there a formula that will look for that letter, count the number of
times it appears in that row and then input it into a specific cell?

Thanks,
Ted
Click to expand...

This array formula will give the number of times the letter "q" appears in
row 1:
=SUM(LEN($1:$1))-SUM(LEN(SUBSTITUTE($1:$1,"q","")))
Note that to enter an array formula you must use Ctrl+Shift+Enter rather
than just enter.
 
Assuming the target row is row1, with
the specific letter to be counted in A2

Try in B2: =COUNTIF(1:1,"*"&A2&"*")

Above assumes that there's only 1 occurrence
of the specific letter in any one cell,
and there's no case sensitivity issue
 
Paul said:
This array formula will give the number of times the letter "q"
appears in
row 1:
=SUM(LEN($1:$1))-SUM(LEN(SUBSTITUTE($1:$1,"q","")))
Note that to enter an array formula you must use Ctrl+Shift+Enter
rather
than just enter. *
Click to expand...


Thanks for the input. Unfortunately, I don't understand the above
formula. Suppose I need to check row G14:AD14 for all occurances of the
letter "p". What would need to be changed/added in order for the
formula to work?

Thanks again!
 
Max said:
Assuming the target row is row1, with
the specific letter to be counted in A2

Try in B2: =COUNTIF(1:1,"*"&A2&"*")

Above assumes that there's only 1 occurrence
of the specific letter in any one cell,
and there's no case sensitivity issue

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email


Thanks for the input. Unfortunately, I don't understand the above
formula. Suppose I need to check row G14:AD14 for all occurances of
the letter "p". What would need to be changed/added in order for the
formula to work?

Thanks again!
Click to expand...
 
Put in A2: p

Put in B2: =COUNTIF($G$14:$AD$,"*"&A2&"*")
(The "1:1" in the earlier formula refers to the entire row1.
It can be replaced by say, a specific range G14:AD14)

If you have other letters to check in G14:AD14 besides "p",
just extend the set-up by putting these other letters
in A3 downwards, i.e. in A3, A4, A5, etc.

Then just copy the formula in B2 down col B as
many rows as you have letters in col A

The above assumes that there's only 1 occurrence
of the specific letter in any one cell,
and also there's no case sensitivity issue
(viz. the case of the letter counted does not matter)

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
t2true said:
Max said:
Assuming the target row is row1, with
the specific letter to be counted in A2

Try in B2: =COUNTIF(1:1,"*"&A2&"*")

Above assumes that there's only 1 occurrence
of the specific letter in any one cell,
and there's no case sensitivity issue

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email


Thanks for the input. Unfortunately, I don't understand the above
formula. Suppose I need to check row G14:AD14 for all occurances of
the letter "p". What would need to be changed/added in order for the
formula to work?

Thanks again!
Click to expand...
Click to expand...
 
G14:AD14 isn't quite a complete row, but to just count the number of p's, you
could use this:

=SUM(LEN(G14:AD14))-SUM(LEN(SUBSTITUTE(G14:AD14,"p","")))
(still an array formula (ctrl-shift-enter))

And if you didn't want to distinguish between P and p (upper/lower case):
=SUM(LEN(G14:AD14))-SUM(LEN(SUBSTITUTE(LOWER(G14:AD14),"p","")))
(ctrl-shift-enter once more)
 
You can set-up Paul's formula in a similar manner:

Put in A2: q (or any specific letter)

Put in B2:
=SUM(LEN($G$14:$AD$14))-SUM(LEN(SUBSTITUTE($G$14:$AD$14,A2,"")))

(Paul's formula amended slightly to suit. The "$1:$1" in the earlier formula
by Paul refers to the entire row1. It can be replaced by say, a specific
range G14:AD)

Just to re-iterate what Paul wrote,
as the above is an array formula, to "enter" the formula,
hold down CTRL + SHIFT, and press ENTER
(instead of just pressing ENTER alone)

Done correctly, Excel will wrap the formula with curly braces {}, viz:
{=SUM(LEN($G$14:$AD$14))-SUM(LEN(SUBSTITUTE($G$14:$AD$14,A2,"")))}

Don't type-in the curly braces yourself.

If you have other letters to check in G14:AD14 besides "q",
just extend the set-up by putting these other letters
in A3 downwards, i.e. in A3, A4, A5, etc.

Then just copy the formula in B2 down col B as
many rows as you have letters in col A
 
Thanks alot guys. You've both been a great help.
Both formulas work like a charm and has saved me a ton of counting.

Thanks again,
Ted
 
Back
Top