Sat 28 Oct 2017

Excel Quiz 56

By |Saturday, October 28th, 2017|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 56

A general quiz to test your Excel awareness

Sat 21 Oct 2017

VBA - Macro to Clean Non-printable characters

By |Saturday, October 21st, 2017|Categories: VBA|Tags: , , , , , |0 Comments

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 -

  1. It will remove non-printable characters with ASCII codes 0 to 31.
  2. It will remove leading and trailing blanks.
  3. Will remove characters with ASCII codes 127, 129, 141, 143, 144, 157, 160.

(more…)

Sat 14 Oct 2017

Challenge 66 - Find the Position of Word

By |Saturday, October 14th, 2017|Categories: Challenges|Tags: , |3 Comments

Suppose you have been given a word say "and" and you need to find which word position is this

  1. In Mr. and Mrs. Smith - Position of "and" is 2 as this is the 2nd word, the position is not 5.
  2. In Samarand Smith and Kittie Smith - the position is 3 not 2 as "and" is appearing in the first word also but not as a single word.

You need to write a formula which finds the word position for the word "and"

Below is the test data.

Mr. and Mrs John Doe
The Reverend John Doe and Mrs. Jane Doe
and
Myranda and Lynda
Rama and
and rama

You can also download an Excel to work on this problem - Challenge 66 - Find the Position of Word

The solution to this challenge will be published after a month i.e. on 14-Nov-17.

Tue 10 Oct 2017

Solution - Challenge 65 - How Many Sundays on Last Date of Months

By |Tuesday, October 10th, 2017|Categories: Solutions|0 Comments

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

 

Sat 07 Oct 2017

Tips & Tricks 164 - All Existing Charts have Disappeared

By |Saturday, October 07th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Accidental Pressing of CTRL+6 toggles hiding / unhiding of all objects which includes charts also. Pressing CTRL+6 will unhide all charts (objects) again.

Sometimes, Charts (objects) can be hidden through Selection Pane (Home tab>Find and Select>Selection Pane > This has Show All / Hide All buttons. In this case, you can show / hide appropriately.

Please note that both above are mutually exclusive. Hence, if use CTRL+6 to hide the objects, you can not use Show All of Selection Pane to show all objects and vice versa.