Suppose you have been given the following and you need to count the number of words in a cell or in a range. Formula for calculating number of words in a cell – =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"") Formula for calculating number of words in a range – =SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))+(TRIM(A1:A100)<>""))
Category: Tips and Tricks
Tips & Tricks 115 – Insert Fixed Current Date and Current Time
We all are aware about today() and now() formulas which insert current date and current date/timestamp. But these change with every recalculation of your worksheet. But if you want to enter the current date and time which doesn't change with recalculation i.e. it gets fixed, then following Excel shortcuts can be used. Current Date –…
Tips & Tricks 114 – COUNTIF for non-contiguous range
All of us love COUNTIF. And it is very easy to do – just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula…
Tips & Tricks 113 – Drag and Drop a Picture in Excel Sheet
Try dragging and dropping a picture in a worksheet – What happens. You will notice a plug sign which means that a picture will be added. But when you release the cursor to drop the picture in Excel sheet nothing happens. Reason – Excel doesn't support dragging and dropping the picture in a worksheet. Now,…
Tips & Tricks 112 – Convert a Number into Years and Months
Suppose, you have been given a number into cell A1 say 26 and you want to display it as 2 Years and 4 Months, you can use following formula – =INT(A1/12)&" Years and "&MOD(A1,12)&" Months" Now, an user can become more demanding and he can say that if month is less than 12, then Years…
Tips & Tricks 111 – Extract URLs from Hyperlinks
In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster Now,…
Tips & Tricks 110 – Financial Functions – Calculate CAGR and AAGR
Many times we get posed with the question of calculating CAGR (Compounded Annual Growth Rate) and AAGR (Average Annual Growth Rate). For layman's not knowing what are these, following are good resources to introduce people to CAGR and AAGR. CAGR – http://www.investopedia.com/terms/c/cagr.asp AAGR – http://www.investopedia.com/terms/a/aagr.asp The formula to calculate CAGR is very simple. RATE function…
Tips & Tricks 109 – Macro to Change between A1 and R1C1 Notations
Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns. The option to do it very simple – File > Excel Options > Formulas > Uncheck R1C1 Reference Style But if that happens…
Tips & Tricks 108 – Extract Date and Time from Date Timestamp
Suppose you have a date timestamp value in cell A1 A1 = 06/14/15 10:15 PM And you want to extract date and time out of this. To extract date, use following formula and format the result cell as date = INT(A1) To extract time, use following formula and format the result cell as time =…
Tips & Tricks 107 – Autofill on a Filtered List
Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill – Article 7 – Generate a Sequence of Numbers Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The…