When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string – =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a","")) In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be…
Category: VBA
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…
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…
Article 27 – Remove Leading Apostrophe
Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more…
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,…
Article 26 – Converting Amount into Words (Indian Currency – Rupees and Paise)
Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh…..unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System) Below are numerical equivalent of Indian words Lakh – 1,00,000 Crore – 1,00,00,000…
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…
Article 19 – How to Check if a cell contains a date
Checking for dates in Excel can be a very interesting problem as Excel stores dates as numbers. For example, 4-Jul-15 is stored as 42189. To elaborate further on this, 1-Jan-1900 is treated as 1. The difference between a date and 1-Jan-1900 +1 is the serial number of that date. hence, in case of 4-Jul-15, it…