About eforexcel

This author has not yet filled in any details.
So far eforexcel has created 419 blog entries.
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: , |0 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.

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: , , , , , , |0 Comments

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.

(more…)

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

The answer key can be downloaded from here Sep-17 eforexcel.com Crossword Answers

(more…)

Sun 10 Sep 2017

Challenge 65 - How Many Sundays on Last Date of Months

By |Sunday, September 10th, 2017|Categories: Challenges|Tags: , , , |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|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , |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).

Sat 26 Aug 2017

Downloads 18 - Sample CSV Files / Data Sets for Testing (till 1.5 Million Records) - Sales

By |Saturday, August 26th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , , , , , |0 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real sales data and should not be used for any other purpose other than testing.

You can download sample csv files ranging from 100 records to 1500000 records. 1.5 Million records will cross 1 million limit of Excel. But 1.5 Million Records are useful for Power Query / Power Pivot. These csv files contain data in various formats like Text and Numbers which should satisfy your need for testing.

This data set can be categorized under "Sales" category.

Below are the fields which appear as part of these csv files as first line.

(more…)