| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of appearance within a cell
In cell A1, I put text "Apple"
How can I count the number of appearance of letter "p" within one cell? I would like the answer come out as numeric "2" Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
Try this:
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"P","")) Note that SUBSTITUTE is case sensitive. So, enter the character you want to count in upper case. Or, you can enter the character in a cell then refer to that cell: B1 = P or p Then: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")) -- Biff Microsoft Excel MVP "Lucy" wrote in message ... In cell A1, I put text "Apple" How can I count the number of appearance of letter "p" within one cell? I would like the answer come out as numeric "2" Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"p","P"},"")))
"Lucy" wrote: In cell A1, I put text "Apple" How can I count the number of appearance of letter "p" within one cell? I would like the answer come out as numeric "2" Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
A1 = St. Louis
=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"S","")) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"p","P"},""))) "Lucy" wrote: In cell A1, I put text "Apple" How can I count the number of appearance of letter "p" within one cell? I would like the answer come out as numeric "2" Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
See inline comments...
A1 = St. Louis =LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails I'm not sure why you posted the above comment... no one posted that formula. =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) The above formula which you posted is the formula that Teethless mama posted in the message you responded to. Rick |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
Yeah, but look how much shorter the UPPER version is!
I thought for sure you'd be "all over it". <BG -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... See inline comments... A1 = St. Louis =LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails I'm not sure why you posted the above comment... no one posted that formula. =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) The above formula which you posted is the formula that Teethless mama posted in the message you responded to. Rick |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
If we assume the text is not longer than 999 characters (the limit can be
made larger if needed), this array-entered** formula really is "much" shorter... =SUM(--(MID(A4,ROW($1:$999),1)="s")) <vbg Rick ** For those reading this thread who are unfamiliar with array-entered formulas, they are committed by pressing Ctrl+Shift+Enter instead of just Enter. "T. Valko" wrote in message ... Yeah, but look how much shorter the UPPER version is! I thought for sure you'd be "all over it". <BG -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... See inline comments... A1 = St. Louis =LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails I'm not sure why you posted the above comment... no one posted that formula. =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) The above formula which you posted is the formula that Teethless mama posted in the message you responded to. Rick |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
That's the Rick I know and love! <g
Oh, thanks for posting that. I've been meaning to do some tests on something and that formula reminded of it. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... If we assume the text is not longer than 999 characters (the limit can be made larger if needed), this array-entered** formula really is "much" shorter... =SUM(--(MID(A4,ROW($1:$999),1)="s")) <vbg Rick ** For those reading this thread who are unfamiliar with array-entered formulas, they are committed by pressing Ctrl+Shift+Enter instead of just Enter. "T. Valko" wrote in message ... Yeah, but look how much shorter the UPPER version is! I thought for sure you'd be "all over it". <BG -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... See inline comments... A1 = St. Louis =LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails I'm not sure why you posted the above comment... no one posted that formula. =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) The above formula which you posted is the formula that Teethless mama posted in the message you responded to. Rick |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
Just out of curiosity, how efficient do you think this formula would be? I'm
not sure what drag the 999-iteration array calculations are putting on the system (I wonder if there is a short cut mechanism built into the MID function evaluator to not attempt to calculate past the end of the text), although I'm guessing the SUBSTITUTE function involves an internal loop of some kind to help offset it. And, of course, if we knew the text would always be less than 100 characters in length, we could reduce the drag from the array calculations by a factor of 10. Rick "Rick Rothstein (MVP - VB)" wrote in message ... If we assume the text is not longer than 999 characters (the limit can be made larger if needed), this array-entered** formula really is "much" shorter... =SUM(--(MID(A4,ROW($1:$999),1)="s")) <vbg Rick ** For those reading this thread who are unfamiliar with array-entered formulas, they are committed by pressing Ctrl+Shift+Enter instead of just Enter. "T. Valko" wrote in message ... Yeah, but look how much shorter the UPPER version is! I thought for sure you'd be "all over it". <BG -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... See inline comments... A1 = St. Louis =LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails I'm not sure why you posted the above comment... no one posted that formula. =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) The above formula which you posted is the formula that Teethless mama posted in the message you responded to. Rick |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
That's the Rick I know and love! <g
I wouldn't want to disappoint, you know... <bg Oh, thanks for posting that. I've been meaning to do some tests on something and that formula reminded of it. Glad to be of help. <g Rick |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
how efficient do you think this formula would be?
Not very. You could limit the iterations by calculating the actual length of the string but then there's go your nice short formula! -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Just out of curiosity, how efficient do you think this formula would be? I'm not sure what drag the 999-iteration array calculations are putting on the system (I wonder if there is a short cut mechanism built into the MID function evaluator to not attempt to calculate past the end of the text), although I'm guessing the SUBSTITUTE function involves an internal loop of some kind to help offset it. And, of course, if we knew the text would always be less than 100 characters in length, we could reduce the drag from the array calculations by a factor of 10. Rick "Rick Rothstein (MVP - VB)" wrote in message ... If we assume the text is not longer than 999 characters (the limit can be made larger if needed), this array-entered** formula really is "much" shorter... =SUM(--(MID(A4,ROW($1:$999),1)="s")) <vbg Rick ** For those reading this thread who are unfamiliar with array-entered formulas, they are committed by pressing Ctrl+Shift+Enter instead of just Enter. "T. Valko" wrote in message ... Yeah, but look how much shorter the UPPER version is! I thought for sure you'd be "all over it". <BG -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... See inline comments... A1 = St. Louis =LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")) Fails I'm not sure why you posted the above comment... no one posted that formula. =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))) The above formula which you posted is the formula that Teethless mama posted in the message you responded to. Rick |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
how efficient do you think this formula would be?
Not very. So I am guessing, then, that the MID function evaluator isn't smart enough to automatically stop calculating when it reaches the end of the text. Although I presume the forced array looping mechanism isn't a speed demon either. You could limit the iterations by calculating the actual length of the string but then there's go your nice short formula! No way! I made a major concession length-wise when I included the absolute reference symbols in the ROW function call just so the formula would be able to be copied down.<g Rick |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
yes, i use this one. Thanks!!
"T. Valko" wrote: Try this: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"P","")) Note that SUBSTITUTE is case sensitive. So, enter the character you want to count in upper case. Or, you can enter the character in a cell then refer to that cell: B1 = P or p Then: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")) -- Biff Microsoft Excel MVP "Lucy" wrote in message ... In cell A1, I put text "Apple" How can I count the number of appearance of letter "p" within one cell? I would like the answer come out as numeric "2" Thanks. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the number of appearance within a cell
You're welcome!
-- Biff Microsoft Excel MVP "Lucy" wrote in message ... yes, i use this one. Thanks!! "T. Valko" wrote: Try this: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"P","")) Note that SUBSTITUTE is case sensitive. So, enter the character you want to count in upper case. Or, you can enter the character in a cell then refer to that cell: B1 = P or p Then: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")) -- Biff Microsoft Excel MVP "Lucy" wrote in message ... In cell A1, I put text "Apple" How can I count the number of appearance of letter "p" within one cell? I would like the answer come out as numeric "2" Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count & Sum Consecutive (2x) appearance of Specific Numeric Values | Excel Worksheet Functions | |||
| Cell appearance | Excel Discussion (Misc queries) | |||
| count each cell that have a number and take that number and count. | Excel Discussion (Misc queries) | |||
| Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
| number formats that allow you to format the appearance of negativ. | Charts and Charting in Excel | |||
Linear Mode
