I have placed this formula in cell "J11". It is designed to tell me how many words are in cell "E11". If there is one word, the phrase "single word" is rerturned. If there are two words, the phrase "2 word phrase" is returned.
I would like to alter the formula so that, if there are three words, the phrase, "3 word phrase" is returned.
All my best efforts have failed
=IF((LEN(TRIM(E11))-LEN(SUBSTITUTE(E11," ",""))+1)>1,"2 word phrase","single word")
Thanks in advance
You can use the "engine" of the above formula to get an answer. You would make this "engine" the first argument in the choose function. Then you could put up to 29 responses (may be more in Excel 2007 and beyond). I have stopped after 5.
=CHOOSE(LEN(TRIM(E11))-LEN(SUBSTITUTE(E11," ",""))+1,"single word","2 word phrase","3 word phrase","4 word phrase","5 word phrase")
This should do what you want.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here