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.

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 17 Oct 2015

Tips & Tricks 113 - Drag and Drop a Picture in Excel Sheet

By |Saturday, October 17th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , |0 Comments

Try dragging and dropping a picture in a worksheet - What happens. You will notice a plug sign which means that a picture will be added. But when you release the cursor to drop the picture in Excel sheet nothing happens.

Reason - Excel doesn't support dragging and dropping the picture in a worksheet.

Now, what to do. If you are dealing with too many pictures, it is cumbersome to use Insert > Picture command.

Fortunately, there is a work around in place. Microsoft Word supports dragging and dropping of pictures. Also good thing is that, you can select all your pictures in one go and drop in Word document.

Now, you can either drag and drop the pictures from the Word document in Excel sheet or you can cut/copy and paste from Word to Excel sheet.

Side Note - OpenOffice / Libre Office supports drag and drop to their respective spreadsheet programs. (Note, but you can not drag or copy and paste pictures into Excel from Open Office / Libre Office spreadhseets like you can drag or copy / paste from Word)

Sat 15 Aug 2015

Article 23 - Correcting Excel (MS Office) Errors Post Windows 10 Upgrade

By |Saturday, August 15th, 2015|Categories: Articles|Tags: , , , , , , , , , , , , , , |0 Comments

Last Updated - 29-Sep-15

Arrival of Windows 10 is something which everybody waited for and users had been loving it. But few users have been experiencing problems with MS Office and particularly with Excel post upgrade to Windows 10. Since, Excel is one of the most important productivity tools which all of us use every day, these problems are impacting productivity as well as delivery to customer. While MS Office on a fresh installation of Windows 10 is working great but for users who upgraded to Windows 10, some of them are feeling unlucky.

(more…)

Tue 04 Aug 2015

Article 21 - Clipboard Error in Excel - We couldn't free up space on the Clipboard

By |Tuesday, August 04th, 2015|Categories: Articles|Tags: , , , , , , , |6 Comments

Many times when working in the Excel following errors may be encountered -

> We couldn't free up space on the Clipboard. Another program might be using it right now
> The clipboard cannot be emptied.  Another program might be using the clipboard

(more…)

Fri 17 Jul 2015

Solution - Challenge 18 – Find the Longest Word in a List

By |Friday, July 17th, 2015|Categories: Solutions|Tags: , , , , |1 Comment

Below is a proposed solution for the challenge - Challenge 18 – Find the Longest Word in a List

Use following formula -

=INDEX(A1:A10,AGGREGATE(15,6,ROW(A1:A10)/
((MAX(INDEX(LEN(A1:A10),,))=LEN(A1:A10))),1))

The solution file can be downloaded from following - Solution - Challenge 18 - Longest Word

 

Sat 13 Jun 2015

Challenge 18 - Find the Longest Word in a List

By |Saturday, June 13th, 2015|Categories: Challenges|Tags: , , , |1 Comment

--- The worksheet related to this problem can be found at Longest Word ---

Suppose you have a list of words in A1 to A10. The challenge before you is to write a formula (preferably non-array) to find the longest word in the list.

In case of ties, pick up the first longest word.

Longest Word

The response can be posted in the comments section of this post.

Sat 07 Jun 2014

Tips & Tricks 10 - Convert from Word Tables to Excel

By |Saturday, June 07th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

The best way to do this would be through utilities. The best one which I found out and is free is following -

http://watermark-images.com/convert-word-to-excel.aspx

Another way is to copy Word tables one by one (or in one go if layout permits) into Excel -

http://office.microsoft.com/en-in/excel-help/copy-a-word-table-into-excel-HP010254130.aspx