| 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
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. |
|
#4
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. |
|
#5
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. |
|
#6
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. |
|
#7
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 |
|
#8
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 |
|
#9
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 |
| 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 | |||
Hybrid Mode
