Below is a possible solution to the challenge Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters. The formula for counting number of alphabets =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),""))) The formula for counting number of numerals =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,""))) The formula for counting other characters =LEN(SUBSTITUTE(A1," ",""))-C1-C2 Where C and C2 are number of alphabets and numerals.
Article 29 – Traffic Lights Conditional Formatting for Project Status for Text R / A / G
We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text. We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light…
Challenge 29 – Reverse (Flip) a Number String
This time challenge is for flipping a number string. Excel VBA has got a function "StrReverse" which can be used to perform this task through VBA. The challenge is to do this through a formula. Let's say A1 = 70948 You formula should return a result of 84907 Note if you number string is 709480,…
Tips & Tricks 116 – Count the Number of Words in a Cell / Range
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)<>""))
Excel Quiz 24
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 –…
Article 28 – How to Unhide all Tabs (Worksheets)
If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same. Option 1 – Use Custom Views 1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the…
Solution – Challenge 26 – Find Sum given multiple OR conditions across columns
Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns. Put following formula for the result – =SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+ COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20)) The workbook illustrating the solution can be downloaded from Solution – Challenge 26 – Find Sum given multiple OR conditions across columns.
Challenge 28 – Create Pascal's Triangle
Pascal Triangle is very famous in number theory. Interested persons can read about Pascal's triangle at following Wikipedia article – https://en.wikipedia.org/wiki/Pascal%27s_triangle Anyway, whether read it at Wikipedia or not, below is Pascal's triangle. One look at it and you will understand the pattern. Now, the challenge before you is following – 1. Put 1 in…