Author: Vijay A. Verma
VBA – Create a Clickable Index (Summary, Table of Contents) Sheet Macro
Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.
Tips & Tricks 166 – Convert a Number to Weekday Name
Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday =TEXT(A1&"Jan2017","dddd") To show only 3 characters of the Weekday Name =TEXT(A1&"Jan2017","ddd") You can add a number to A1 if you want to show some other Weekday Name Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just…
Solution – Challenge 66 – Find the Position of Word
Below is a proposed solution for the Challenge 66 – Find the Position of Word Put the following formula and drag down. Let's assume the word "and" in cell D2 – =LEN(REPLACE(A2,SEARCH(" "&$D$2," "&A2),LEN(A2),""))-LEN(SUBSTITUTE( REPLACE(A2,SEARCH(" "&$D$2," "&A2),LEN(A2),"")," ",""))+1 You may download the solution workbook – Solution – Challenge 66 – Find the Position of Word
VBA – Function to Validate IP Addresss
Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255 You can put following type of construct in a cell =IsValidIP(A2)
Challenge 67 – Find Duplicates and Show the Count
You have been given following data and you need to show corresponding count. You need to write a formula which can generate this count. If needed, you can use a maximum of helper column also. Download problem workbook from Find Duplicates and Show the Count The solution to this problem will be published after a…
Tips & Tricks 165 – Convert Weekday Names to Numbers
Suppose Cell A2 contains weekday names like Sunday, Monday…..(or Sun, Mon…), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7. =ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0) =MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0) If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1…
VBA – Macro to Clean Non-printable characters
Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way – It will remove non-printable characters with ASCII codes 0 to 31. It will remove leading and trailing blanks. Will remove characters with ASCII codes 127, 129, 141, 143,…
Challenge 66 – Find the Position of Word
Suppose you have been given a word say "and" and you need to find which word position is this In Mr. and Mrs. Smith – Position of "and" is 2 as this is the 2nd word, the position is not 5. In Samarand Smith and Kittie Smith – the position is 3 not 2 as…
Solution – Challenge 65 – How Many Sundays on Last Date of Months
Below is a possible solution to the Challenge 65 – How Many Sundays on Last Date of Months =SUMPRODUCT((ROW(INDIRECT($B$1&":"&$B$2))=EOMONTH(ROW(INDIRECT( $B$1&":"&$B$2)),0))*(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"ddd")="Sun")) Below is an Excel sheet containing the solution Solution – Challenge 65 – How Many Sundays on Last Date of Months