Counting words in cells

Is there a way of counting the number of words in a cell, basically an Excel version of the NUMWORD field in Word
If not, if there a clever trick I can use to end up with the same result?
0
anonymous (74722)
4/5/2004 1:11:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
279 Views

Similar Articles

[PageSpeed] 36

Hi
try the following to count word
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

-- 
Regards
Frank Kabel
Frankfurt, Germany


BeechBoy wrote:
> Is there a way of counting the number of words in a cell, basically
> an Excel version of the NUMWORD field in Word? If not, if there a
> clever trick I can use to end up with the same result? 
0
frank.kabel (11126)
4/5/2004 1:18:27 PM
Try the following formula:

=IF(TRIM(A1)="",0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"BeechBoy" <anonymous@discussions.microsoft.com> wrote in message
news:5987FE5C-CCEF-4BCA-AB8D-EE8C77A8D837@microsoft.com...
> Is there a way of counting the number of words in a cell, basically an
Excel version of the NUMWORD field in Word?
> If not, if there a clever trick I can use to end up with the same result?


0
chip1 (1821)
4/5/2004 1:21:56 PM
Brilliant!   Both work equally well (although Frank's returns 1 for empty cells - not a problem for me though)
Thanks very much for your quick replies
Cheers
0
anonymous (74722)
4/5/2004 1:41:03 PM
Reply:

Similar Artilces:

formatting cells for radar graph
Is there a way to format cells so that when you type in a whole number, it is converted to a decimal? (EX: If I type in 150, I want the cell to format this whole number as .150) I have a radar graph that I have set up to graph product thickness, when the thickness is less than a whole number. Thanks Hi Debbie, Yes: use Tools|Options and open Edit tab; locate Fixed Decimals and set it to 3 However, I think this is "global" and will apply to all workbooks you subsequently open; so do remember to set it off. Alternative: enter the three digit numbers; type 1000 in an empty ce...

Too many cell formats #3
I am wondering if there is any way for me to find out how many cell formats I am using. I know the linit is 4000 and I hve a user that uses very close to that. Is there a report or a third party app that would let me check my sheets fo this information? Thanks and Happy Holidays. I'm having difficulty understanding why there would be so many cell formats? What purpose does this serve? "Adrian" wrote: > I am wondering if there is any way for me to find out how many cell formats > I am using. I know the linit is 4000 and I hve a user that uses very close > to th...

Format Cells Custom Forumula?
How do I interpret (#,##0_);[Red](#,##0) or other similar formulas so I can figure out what is wrong or create a new one for a specific situation? I looked throughout help but couldn't find anything. I know this one rounds the value of the formula and spaces it so if it is a negagive it doesn't mess up the spacing on the page, but I don't know how to read the actual formula to determine what does what. Thanks, Mike See "About custom number formats" in XL Help. In article <F2FEC468-63AE-4F9C-BE97-ED44A6D5CDED@microsoft.com>, Mike King <Mike King@discussi...

my formated column is displaying one cell incorrectly
I have a spreadsheet with 4 columns; the 4th columns is a formula going across the 3 columns a+b-c = d with d preferably equally zero. When I format column d almost all of the columns that equal zero display as a dash (preference). However periodically one or two of the rows will display as 0.00 and I can't seem to get the formatting to change to the dash. I have tried to reformat all columns all other cells and to no avail. Has anyone else run into this issue and if so how have u solved it. The only way I can solve it is to actually put a zero in the cell. If the numb...

Macro/keyboard shortcut to increment a cell value
I would like to set up a keyboard shortcut (e.g., Ctrl+I) that would increment the value of the selected cell by 1. Can someone get me started by pointing me in the right direction? I have some experience creating simple macros in Word, if that is what's needed. Thanks -- PC: HP Omnibook 6000 OS: Win 2K SP-4 (5.00.2195) LAN: P2P with an HP Vectra workstation Email: Usenet-20031220 at spamex.com (11/03/04) One way is to make a macro and assign the shortcut key to it: Option Explicit Sub AddOneToActiveCell() With ActiveCell If IsNumeric(.Value) Then .Value = .Val...

Referencing a Range of Cells
Hi all, I have named a range of cells on one worksheet, say I called (A1:B10) as "My_Range" on "WorkSheet_1". And then I got another worksheet which want to copy the same value on another worksheet "WorkSheet_2" using the name as a reference. How could I do this? Also, could I specify a particular value inside the named range? For example how could I display the B3 cell in "My_Range"? Thanks for your help. Derek C hi. not sure why you would want to do it this way but try this Sub macSetRange() Dim rng As Range 'range to copy Dim rng1 As...

Code to read a cell from every workbook in a folder
Any Ideas about what code would read the formula in cell c3 of ever workbook in a folder and give an array of the workbook names and th formula??? -- Message posted from http://www.ExcelForum.com Something like this? Option Explicit Sub testme01() Dim myFiles() As String Dim fCtr As Long Dim myFile As String Dim myPath As String Dim tempWkbk As Workbook Dim wks As Worksheet Dim oRow As Long Dim logWks As Worksheet Dim myAddr As String myAddr = "C3" 'change to point at the folder to check myPath = "...

format cell
How to set a cell so that when I key in [number] follow by [/] follow by [number] and display as number - number I.e key in 3/4, appear as 3 - 4 Not by formatting alone. You could format the cell as text (not general, not a date) and use the adjacent cell to return the string the way you want to see it: =substitute(a1,"/"," - ") After you do all your data entry, you could hide the original column or even convert the formula column to values and delete the original column. crapit wrote: > > How to set a cell so that when I key in [number] fol...

Can you change only one cell size in a workbook?
No. You can change the width of a column, or the height of a row. And next time, please remember to ask the question in the body of the message, not just in the subject line. And if this is related to charting, it is not clear how. Was this intended for one of the other Excel groups? -- David Biddulph "Becky" <Becky@discussions.microsoft.com> wrote in message news:4A4504F7-4FA9-44C4-A79C-5E67A9E8C169@microsoft.com... > ...

Clearing Cells
Hi I'm using an Excel sheet for pricing items customers purchase. In A2 I could enter any of 4 prices e.g. £30 , £15, £10, £5 In B2, C2, D2, E2, I have entered the following formula =IF(A2=Rate_30,A2,0) It follows that B2 shows £30 and C2,D2,E2 show 0.00 that works ok the problem is with 200 rows its a lot of zero's is it possible to add to the formula something that clears all the zero's so that the columns are easier to read. This sheet is used on a daily basis so the customer could call in tomorrow and buy something at £15 the £30 would c...

a macro to copy only 2 lines w/i a cell separated by 4 line breaks
Can anyone help me determine how to copy only 2 specific lines from a cell that contains text in 4 lines that is separated using the line break function? Which two lines? You may be better off parsing into 4 cells then picking the two you want Gord Dibben MS Excel MVP On Wed, 14 Apr 2010 15:31:01 -0700, poltpar <poltpar@discussions.microsoft.com> wrote: >Can anyone help me determine how to copy only 2 specific lines from a cell >that contains text in 4 lines that is separated using the line break function? ...

Word 2007 Malfunctions
I am running MS Office 2007 on Windows 7. Word runs very, very slowly and frequently malfunctions. Print menus and other menus appear transparent and unusable. Sometimes docs saved to the server cannot be changed. When using spell check in Outlook, it can take two+ minutes to spell check a brief email equivalent to a half page of text on an 8.5" x 11" sheet of paper. There appears to be some kind of problem in the interface with Windows 7 and MS Office. Please send ideas for solving this problem. It is most irritating. ...

error in word document
Every so often I get a message that word has to shut down due to some error, unfortunately I havent managed to get details of the error. This started a few nights ago after my wife used my PC so suspect maybe something she has done. Is there anything I can do or try to correct this, at least until I can try and get the error message. What happens is I am typing away and the out of the blue it `errors` and has to shut down and I lose most of the work. It does not appear to happen every time. Any guidance apprecitaed. thanks While others are figuring out what went wr...

set excel cell value
i'm no expert at vba and have been having a hard time trying to send a value from an open access form to a specific cell in excel. I've been writing a module in Ac2000 that opens excel, names the sheet, and sends some Access table data over. Thanks to Bob Larsons code I was able to do most of this. After a week of trial and error i'm now also able to format the sheet the way it needs to be, plus some conditional formatting that seems to be working out great- now i'm very badly stuck on trying to send a value from an open access form to a particular cell in the shee...

conversion from word to publisher
my computer crashed. several documents originally writen with pub got recovered but now show up in an unreadable word document. I reloaded publisher, which had disapeared in the crash, and I want to convert those word docs into back to publisher. Howdoidothat? Jojo Well, you could change the extension to .pub if you really think the files are Publisher's. What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jojo" <Jojo@discussions.microsoft.com> wrote in message news:EAB856D1-6E2A-4505-9998-E...

Copying an Excel spreadsheet to a Word document
Up until now I have only had to copy either Excel charts or small Excel ranges to Word. I'm trying to copy a whole spreadsheet (which is sized to fit on one Excel page when printed) but I can't manage to fit it into a single Word page. I don't see any sizing handles; I trying playing with the Word document orientation and margins but nothing has worked. This is Excel 1.01 but I am really stuck. What's the way around this? Select the range, hold down shift while clicking edit and select Copy Picture then paste into Word, that will give you size handles -- Regards, Pe...

is it possible to change print ink color based on cell value?
Is it possible to change print ink color based on value in a cell for a spreadsheet application? Example: If a cell's value is over 250, can I have it print the number in red ink to draw my attention to it?? You can use Conditional Formatting (Format>Conditional Formatting) to test the value and set the font colour accordingly. -- HTH RP (remove nothere from the email address if mailing direct) "Dave The Mechanic" <Dave The Mechanic@discussions.microsoft.com> wrote in message news:0B63AB99-5824-4AFB-A206-D4CDF1BB9D48@microsoft.com... > Is it possible to chang...

custom a cell
How do make that when I enter 1234 its should come out 12.3 -- shlom ----------------------------------------------------------------------- shlomo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=34680 Format > Cell > Number > change decimal and symbol -- Cissy ------------------------------------------------------------------------ Cissy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20107 View this thread: http://www.excelforum.com/showthread....

Cannot paste in Word 2003
I can copy but not paste. Do I need to remove the office live add-in 1.3? Laura There are cases when (poorly designed) add-ins cause issues. If you believe that the Office live add-in is responsible for your pasting problems, try uninstalling it. -- Stefan Blom Microsoft Word MVP "Laura" wrote: > > I can copy but not paste. Do I need to remove the office live add-in 1.3? > Laura ...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

Moving cursor to another cell w/out selecting entire area
PLEASE write your question in the body of the post and NOT just the subject Have a look here: http://dts-l.org/goodpost.htm -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk "Darin" <Darin@discussions.microsoft.com> wrote in message news:525460EF-810C-4599-A841-2940CB8E0471@microsoft.com... > PLEASE write your reply in the body of the post and NOT in the subject line. Have a look here: http://dts-l.org/goodpost.htm -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk "Ragdyer&qu...

Pasting Excel charts into Word
Whenever I paste Excel charts into Word, the fonts end up looking terrible. Is there an easy solution? Pasting as a picture does not work. The only thing that does work is right clicking on the chart while in Word, selecting "Edit Picture", then fixing the text that has changed position and direction. But then my chart is no longer a simple picture. Help! How are you pasting it? I have no problem using the Word Insert Object command. 1 - In Excel, have the chart saved in its own separate .xls file, no other worksheets. 2 - In Word, choose Insert>Object. 3 - Click on the Cre...

count values
Hi everibody In column 1 are cells with different values (some cells have 10, other have 100 other have 500 etc); How can excel say "there are X cells with value 10, Y cells with value 100, Z cells with value 500 etc."? Thanks a lot Look in the help index for COUNTIF -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "neerav" <neerav@discussions.microsoft.com> wrote in message news:F40046DB-74B3-4F06-BC99-0853F0ECFCAB@microsoft.com... > > Hi everibody > In column 1 are cells with different values (some cells have 10, other ...

Learning to Use Word 2007 as a Blind Person
I'm transitioning from Word 2000 to 2007 and would like to know if there's a way for a blind user like me using the screen-reading program called JAWS 10 )from Freedom Scientific) to learn Word 2007 quickly and thoroughly. Thank you for your feedback! I don't mean to be dismissive, but sighted people have a lot of trouble making the transition from the older Word interface to that of 2007. I don't know anything about the screen reading program, but frankly I don't see it helping you learn 2007 *quickly*. It took me over a year to become comfortable with 200...

Link from Word to Excel
Is it possible to link a date from Word to Excel, so that when you change the date in Word, it will update in the Excel file. I can do this easily the other way around (from excel to Word), but there is a project at work that requires the dates to be changed in the existing Word documents first. Thank you for your help. ...