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||1 Comment

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.
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||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.

Sat 30 Sep 2017

## Tips & Tricks 163 - Convert Alphabets to Numbers

By |Saturday, September 30th, 2017|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

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

Sat 23 Sep 2017

## VBA - Macro to Combine (Append) Sheets

By |Saturday, September 23rd, 2017|Categories: VBA|Tags: , , , , , , |1 Comment

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".

1. In case of many sheets, it will merge all sheets.
2. If you don't want to merge all sheets but few sheets, just create two sheets named "Start" and "Finish" and move all sheets between these Start and Finish. The macro will merge all sheets between Start and Finish.
Sat 16 Sep 2017

## Excel Quiz 55 - Crossword VIII

By |Saturday, September 16th, 2017|Categories: Quizzes|Tags: , , |0 Comments

Time for Crossword Again after a long time. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Sep-17 eforexcel.com Crossword

Sun 10 Sep 2017

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

By |Sunday, September 10th, 2017|Categories: Challenges||2 Comments

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 month i.e. on 10-Oct-17.

Sun 03 Sep 2017

## Tips & Tricks 162 - Convert a Month Name to Month Number

By |Sunday, September 03rd, 2017|Categories: Tips and Tricks||0 Comments

Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number

=MONTH(1&A1)

=--TEXT(1&A1,"m")

In case, cell A1 contains  the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).