Sat 25 Nov 2017

## VBA - Create an Index (Summary, Table of Contents) Sheet Macro

By |Saturday, November 25th, 2017|Categories: VBA||1 Comment

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

Sat 18 Nov 2017

## Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

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 add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")

Tue 14 Nov 2017

## Solution - Challenge 66 - Find the Position of Word

By |Tuesday, November 14th, 2017|Categories: Solutions|Tags: , |0 Comments

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

Sat 11 Nov 2017

## VBA - Function to Validate IP Addresss

By |Saturday, November 11th, 2017|Categories: VBA|0 Comments

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)

Thu 09 Nov 2017

## Challenge 67 - Find Duplicates and Show the Count

By |Thursday, November 09th, 2017|Categories: Challenges|Tags: , , |2 Comments

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 month i.e. on 9-Dec-17.

Sat 04 Nov 2017

## Tips & Tricks 165 - Convert Weekday Names to Numbers

By |Saturday, November 04th, 2017|Categories: Tips and Tricks||0 Comments

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 and Sun = 7

=ROUND(SEARCH(LEFT(A2,2),"MoTuWeThFrSaSu")/2,0)

=MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)