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
Tips & Tricks 163 – Convert Alphabets to Numbers
If you want to convert a, b, c….z to 1, 2,3….26, the you can use following type of formula – =CODE(UPPER(A1))-64
VBA – Macro to Combine (Append) Sheets
Many times, we need to combine worksheets together. Below is a Macro to do this. You just need to change the parameter in "Change Parameters in this Section". In case of many sheets, it will merge all sheets. If you don't want to merge all sheets but few sheets, just create two sheets named "Start"…
Challenge 65 – How Many Sundays on Last Date of Months
This time you need to work out the number of Sundays on last date of the months between two given dates. For example, between two dates of 1-Jan-2017 to 31-Dec-2020, total number of Sundays on last date of the months is 6 as highlighted below. The answer to the solution will be published after a…