Counting Texts

If cell A1 contains a paragraph of texts and if I want to count just letter 
"W"s (Upper or lower case), How can I achieve this?

Example: A1 contains "How now brown cow"  the formula should return "4".

Thank you.


0
Keith1 (152)
7/29/2009 9:51:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
563 Views

Similar Articles

[PageSpeed] 12

Write a macro and use the VBA functions Instr(strName, "w")  together with 
Split(strName, "w") and count the number of times that it finds "w" or "W"

Chris

"Keith" <Keith@discussions.microsoft.com> wrote in message 
news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com...
> If cell A1 contains a paragraph of texts and if I want to count just 
> letter
> "W"s (Upper or lower case), How can I achieve this?
>
> Example: A1 contains "How now brown cow"  the formula should return "4".
>
> Thank you.
>
> 


0
ng4206 (77)
7/29/2009 10:23:25 PM
Try this spreadsheet formula:

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

since SUBSTITUTE is case sensitive you can modify this to read

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"w",""))

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Keith" wrote:

> If cell A1 contains a paragraph of texts and if I want to count just letter 
> "W"s (Upper or lower case), How can I achieve this?
> 
> Example: A1 contains "How now brown cow"  the formula should return "4".
> 
> Thank you.
> 
> 
0
7/29/2009 11:29:10 PM
Thanks so much Shane.  Your suggestion worked perfectly.

Thanks again

"Shane Devenshire" wrote:

> Try this spreadsheet formula:
> 
> =LEN(A1)-LEN(SUBSTITUTE(A1,"w",""))
> 
> since SUBSTITUTE is case sensitive you can modify this to read
> 
> =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"w",""))
> 
> -- 
> If this helps, please click the Yes button.
> 
> Cheers,
> Shane Devenshire
> 
> 
> "Keith" wrote:
> 
> > If cell A1 contains a paragraph of texts and if I want to count just letter 
> > "W"s (Upper or lower case), How can I achieve this?
> > 
> > Example: A1 contains "How now brown cow"  the formula should return "4".
> > 
> > Thank you.
> > 
> > 
0
Keith1 (152)
7/30/2009 12:01:01 PM
Reply:

Similar Artilces:

Stationery created in outlook looks great, outlook express shows it as plain text tho?
I just created some stationery in Outlook 2003 using dreamweaver. I placed the html file along with a single graphic into the stationery folder and made sure there were no absoulte path references in the HTML code. In Outlook I could then select the stationery and it looks perfect. Test emails sent to myself appear perfect in outlook. I sent a test email via Outlook 2003 and received the email in Outlook Express but it displays it as plain text??? in outlook express I selected the same stationery as outlook and sent a test email to myself which looked fine in outlook express and outlo...

Calculating age in a label or text box on a form
Hello All, I have a text box for birthdate for a user to enter the birthdate and I want to the age for the user to be automatically calcuated and displayed in another label called "Age." What formula can I use? Missy ...

Printing deleted text/ clipart
I am printing labels... and the first few I highlighted and inserted a new clipart and text. It still prints the old text/art along with the new....even though the print preview only shows the new text/clipart. I have deleted the new picture and text and then redid entered the text. Print preview shows only the new text...when I print it, the old text is behind it. I am confused because no where can you see the old text unless you print it out. This is word 2007..small circle labels. Any reason for this or how to get rid of it? thanks -- Michelle Which steps are you perform...

save a column of text as a file
How do I select a column of data and save it as an external file ( say, col.txt)? One more thing, is there a way to have blank entries automatically skipped?! 1 Save the worksheet as an Excel file before doing anything! 2 Insert a new worksheet: Insert|Worksheet 3 Select and Copy the column to be saved 4 Move to A1 in new worksheet; use Paste but if the column has formulas use Paste Special -> Values 5 Select column; use Data | AutoFilter; use auto filter to display Non-Blanks 6 Copy and paste these non-blanks to column B 7 Delete column A 6 Use File | Save As and specify Type to be TXT...

Text missing from email shows in autopreview
Hi, This is weird. I have autopreview on, and from some people (some are using lotus notes, but I can't say that all are conclusivly) whenever I get an email from them, weather they have generated it or have responded to one of mine, their text does not show up when I open the email. BUT, I can see the first line or two in autopreview. If I drag the email to the desktop, and open it with notepad, I can see the entire text, so it's in there. Any thoughts on why? or how to fix it? Thanks, -Vin ...

VBA: text same color as cell background color
I'm using the following code (compliments of Otto Moehrbach)to color cells a specific color, depending on cell contents: Sub ChangeColor() Dim Cell As Range Dim RngToCheck As Range With Sheets("Chart") Set RngToCheck = .Range("B9:G20", .Range (.UsedRange.Address)) End With For Each Cell In RngToCheck With Sheets("Chart") Select Case .Range(Cell.Address).Value Case "EX" Cell.Interior.Color = vbMagenta Case "CS" Cell.Inter...

Text box
When I print the Text box, I must have several lines in the Text box below the last line, otherwise the last few lines in the Text box do not print. So how can I shrink the Text box without losing the last few lines. Thanks. -- Ed In article <0BFC6F3F-3915-4C52-AD49-E87B605AD29D@microsoft.com>, Ed <Ed@discussions.microsoft.com> wrote: > When I print the Text box, I must have several lines in the Text box below > the last line, otherwise the last few lines in the Text box do not print. So > how can I shrink the Text box without losing the last few lines. Which ap...

Setting text of an ActiveX control subclassed from BUTTON
Greetings! I want to subclass an ActiveX control from a button. My little test control works just fine, except that I don't know how to put text into it. Could someone tell me how to do that? This same exact question was asked about six years ago. The answer pointed to a sample of subclassing a listview control, and it showed how to send an LV_INSERTTEXT message to the control. If the answer is to use SendMessage(), what message should I send? Thanks very much! Rob Richardson Rad-Con, Inc. OK, as usual, I jumped the gun. I went back and looked at the knowledge base article, and ...

Email Text Dissappears when hit save
For some reason. Email text dissappears when we hit save. Even under my administrator aaccount. ...

Counting negative values
Hi All, I'd be very grateful if someone could help me with this. I have a following table: No, Company,Var1 12, ZXC, 6 12, ZXC, 4 12, ZXC, -3 34, AAC, 2 34, AAC, 4 56, BBR, -5 78, CCV, -4 78, CCV, -12 What I need is another column with some variable that would turn e.g. 1 when company has at least one row with negative value of Var1 and 0 if not. In other words, i'd like to get: No, Company,Var1, Var2 12, ZXC, 6, 1 12, ZXC, 4, 1 12, ZXC, -3, 1 34, AAC, 2, 0 34, AAC, 4, 0 56, BBR, -5, 1 78, CCV, -4, 1 78, CCV, -12, 1 I have to add that I have about 9000 rows and i'm using exc...

Writing text from a combined text field to a table text field
I have a dumb question that is stumping me (I'm a spotty Access user -- use it for a while and become fairly proficient and then don't use it for a while and forget things). I have a table which has four fields: ID (autonumber) ProjectNumber (Text) ProjectCo (Text) ProjectName (Text) I have a form which has control source for the "ProjectNumber" field as - "PN-"&[ID] - which, on the form displays "PN-" followed by the autonumber ID field value -- so, for example for record #1 it shows "PN-1". My problem is, I want that value (the "PN...

Adding text to all outgoing messages...
I would like to add a text message to all outgoing mail messages. How can i do this ? Carlos for your text (assuming you are usaing exchange)... http://www.gfi.com/downloads/downloads.aspx?pid=ME&lid=en They provide a free version that does disclaimers. "Carlos Mendes" wrote: > I would like to add a text message to all outgoing mail messages. How > can i do this ? > > Carlos > check this vbs sample http://www.aloaha.com/article/eventsink.html "Carlos Mendes" <cjmendes@confetil.com> schrieb im Newsbeitrag news:51esu09sh1tpfl4usbfbht083cq970...

Text in pivot table
I'm aware that summary functions will not work for text data in Excel 2000 (eg something like 'first' in Access crosstabs doesn't exist). Two questions: - Is this functionality available in later versions? - What are the alternatives (eg anyone got some VBA they've used to do it)? Thanks GB Pivottables are still quantitative summaries. You may want to give more info about what your data looks like and what you want. I might lead to better alternatives. green biro wrote: > > I'm aware that summary functions will not work for text data in Excel 2000 > (...

Counting instances in a cell
Hello, How can I count the number of times the number 4 repeats itself in a single cell? Example: I have 456444 in A1 and I would like to know how many time the number 4 appears in the cell. Any help would be appreciated. Thanks, Jose R. One way =LEN(A1)-LEN(SUBSTITUTE(A1,4,"")) Regards, Peo Sjoblom "jose@aaacheckmate.com" wrote: > Hello, > > How can I count the number of times the number 4 repeats > itself in a single cell? Example: I have 456444 in A1 > and I would like to know how many time the number 4 > appears in the cell. Any h...

count #3
see attachment Attachment filename: quarter.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54020 -- Message posted from http://www.ExcelForum.com see http://cpearson.com/excel/newposte.htm In article <malay_ko.162jsz@excelforum-nospam.com>, malay_ko <<malay_ko.162jsz@excelforum-nospam.com>> wrote: > see attachment. ...

Counting only displayed cells in the column
I have a worksheet that I would like to be able to count only the displayed cells in a column i.e. I do not want to count hidden cells (rows). If all rows are displayed the total count should equal all rows with contents (counta function), if not - count only those rows displayed. Hi there. There are, at least, two choices: =SUBTOTAL(2,A2:A11) count only the cells which are not hidden by means of filters. OR =SUBTOTAL(102,A2:A11) count only cells which are not hidden either manually or by means of a filter. Regards, Otávio "CEG_Staffer" wrote: >...

Text of a RecordSource
Hi, ich have a Report with a recordsource. the source is a query. when call the function Me.RecordSource, i get the queryname. is there a possibility to get the text of this query ?? And how ?? thx a lot Martin Martin wrote: > Hi, > > ich have a Report with a recordsource. > the source is a query. > > when call the function Me.RecordSource, i get the queryname. > is there a possibility to get the text of this query ?? > And how ?? > > > thx a lot > Martin CurrentDB.QueryDefs("QueryName").sql -- Rick Brandt, Microsoft Access MVP Email (as...

text box LINK #2
How do you link a text box so that you type a heading one time, but it also appears automatically in another text box within the document? Not sure if link is the correct word? I am using Publisher 2002. Thanks. You cannot. You can also copy and paste the Text Frame many times. You can create a Merge file if you have many to do. "Elena" <Elena@discussions.microsoft.com> wrote in message news:499CAE09-F0A0-400B-AA03-5302E7F026BB@microsoft.com... > How do you link a text box so that you type a heading one time, but it > also > appears automatically in another ...

Right clicking on text does not bring up hyperlink option
I want to add a hyperlink to another area in my Excel workbook. On one sheet when I right click on text that I want to add the hyperlink to, I don't get the hyperlink option. It has the following: cut copy paste paste special __________ insert page break reset all page breaks insert delete clear contents __________ insert comment __________ format cells set print area reset print area page setup There is no hyperlink option. Any ideas? Thanks in advance. The cell menu (the one you get when you rightclick on a cell) is different if you're in View|Normal or View|Page break previ...

Is any method to retrieve text from the point in richeidt?
I are working on showing some tip when the mouse cursor hovers on the richedit. in fact, I need a method(just like "hittest") to retrieve the text or the position of the text. so thanks for any repliy :-) -- Frank F.Han +-----------------------------------------+ | winsays@:-)hotmail:-).com | +-----------------------------------------+ Frank, Sending EM_CHARFROMPOS to the control will return the index of the character closest to a given coordinate pair, I think you should start there. Johan Rosengren Abstrakt Mekanik AB "Frank" <winsays@hotmail.com&...

Count / Frequency
Hi, I have a Dynamic Named Range called ROOM_TYPE which consists of tex based names - Example: Executive, Admin, Manager, Trainees, Graphics. The room names appear in a single column defined as ROOM_TYPE. Row No ROOM_TYPE Count ROW5 Executive 1 ROW6 Executive 2 ROW7 Executive 3 ROW8 Admin 1 ROW9 Manager 1 ROW10 Admin 2 ROW11 Graphics 1 ROW12 Manager 2 ROW13 Trainees 1 ROW14 Graphics 2 ROW15 Manager ...

Sum column values that contains text &/or dates
Column D3 thru D43 contain a mixture of values & dates. For example: D3 = 16,026.76 D4 = 11-18-05 D5 = blank D6 = 6,855.34 D7= 11-03-05 D8 = blank D9 = 5,270.00 D10 = 11-02-05 Need sum to equal 28,152.10 (total only values from entry or formulas) Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=489114 If your dates are text strings which say "11-18-05"...

cross-hatches appearing in place of text
I'm sure it's something simple, but what's causing a block of teext I'm copying-and-pasting into a wide cell to turn into cross-hatches? I've tried setting the alignment to Top and Left, and adjusting the cell's size, but the text keeps popping up with only a line displaying, or as the line of cross-hatches. Kevin check a) the format for the cell and b) the length of the text in the cell. First, try setting the format for the cell to General. If the cell is formatted as Text and has more than 255 characters in it, it will display as ###...### Regards Trevor &...

Counting yes/no fields with differrent names
Hello, I have a database that contains the following fields, some of which (all fields starting with Page_) are selected via check marks; ID, Date_Received, Site_Number, Subject_Number, Subject_Initials, Page_1, Page_2, Page_3, Page_4, Page_5, Page_6, Page_7, Page_8, Page_9, Page_10_A, Page_10_B, Page_10_C, Page_10_D, Page_10_E, Page_11_A, Page_11_B, Page_11_C, Page_11_D, Page_11_E, Page_12, Page_13_A, Page_13_B, Page_13_C, Page_13_D, Page_13_E My intentions are to provide a total page count for each subject. My problem is that I am not really sure on how to accomplish this. Any th...

Inserting Word Count in a document
Hello, I know there is a word count tool in Microsoft Word, but is there any way that number can be inserted in the document anywhere, i.e. the header or the footer? Thanks! Insert the NUMWORDS field where you want the word count. Note that this field (like most fields) won't update automatically. If you choose to insert it in the header or footer, you can update it by switching to Print Preview and then back to your favorite view. -- Stefan Blom Microsoft Word MVP "MacNut2004" <MacNut2004@discussions.microsoft.com> wrote in message news:C41...