Suppose, you have a square of 8×8 like below The challenge is following – You need to come up with a formula that sums up the square diagonally. L1 contains the value which tells us how many elements to consider. L2 has T or B (T – from Top, B – from Bottom) which tells…
Tips & Tricks 63 – Short Cut Key for Opening Goto Special Dialog Box
Remember EGS (EGGS with one G missing) Just Press ALT and hold it. Now Press E and leave it, then press G and leave it and then press S and leave S as well as ALT. And you will have Goto Special Dialog Box.
Tips & Tricks 62 – Set the Scroll Area of a Worksheet
If you want to set a scroll area in a worksheet, for example A1:G50. User will not be able scroll beyond row 50 and column G. To do this – 1. Right Click on a Sheet tab and click View Code. OR Press ALT+F11. Now VBE Editor will open. 2. Set A1:G50 in Scroll Area…
Challenge 1 – Single Formula for Fibonacci Numbers
Fibonacci Numbers is a series of numbers where next number is found by adding two previous numbers. It starts with 0, followed by 1 and summing up 0 and 1 gives next number as 1. Now sum up previous two numbers 1 and 1 and result is is 2. Next number would be 1+2 =…
Tips & Tricks 61 – Roman Representation of Numbers
Use ROMAN function. Hence ROMAN(56) will give LVI. ROMAN works only for numbers 1 to 3999.
Tips & Tricks 60 – Remove numbers from string
To remove numbers from a string (for example Vij1aY A. V4er7ma8 contains numbers which are not required), we can use nested SUBSTITUTE function to remove numbers. Use below formula assuming string is in A1 cell – =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"") Note – Since this formula is in multiple lines, hence you will have to copy this…
Article 6 – Convert Text Format into Number Format
This article was long pending after I wrote Covert Number Format into Text Format. This post essentially deals with the fact that if a cell has number in text format, how to convert that value into Number format so that it becomes usable for calculations and for other purposes. (Exclusion – If a number has…
Tips & Tricks 59 – Remove Comments from All Sheets in a Workbook
In an earlier tip, I talked about how to remove all comments from a sheet. But that works on a sheet. If you want to remove from all sheets in one go rather than doing for every sheet separately, use following trick – 1. Office Button > Prepare > Inspect Document 2. A warning message…
Tips & Tricks 58 – Remove Alphabets from a String
If your string is in cell A1, use following formula to remove all alphabets from a string =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(LOWER(A1),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""), "g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""), "p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z","")
Tips & Tricks 57 – Remove All Comments from a Sheet
1. Home Tab > Find and Select > Comments – This will select all the cells where comments are there. 2. Home Tab > Clear > Clear Comments – This will clear all comments from the sheet