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 30 Jul 2016

Challenge 48 - Whether a Sentence is Pangram or Not

By |Saturday, July 30th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

This time, I decided to pose a challenge on English words. A Pangram is that sentence in English which uses all 26 alphabets of English language at least once. A very common example is the very famous sentence "The quick brown fox jumps over the lazy dog" which contains all alphabets of English language at least once. Below link contains many other examples of Pangram.

http://www.rinkworks.com/words/pangrams.shtml

The challenge before you is to write a formula which gives the output "Pangram" or "Not Pangram" depending upon the fact if cell A1 has a Pangram sentence or not. Apart from English alphabets, the sentence can contain blanks, dots and commas and no other characters.

The given link contains many examples for Pangram which you can use as your test cases. Some examples from the above site -

A mad boxer shot a quick, gloved jab to the jaw of his dizzy opponent.
Big Fuji waves pitch enzymed kex liquor.
We quickly seized the black axle and just saved it from going past him.

The solution to this challenge will be published after a month i.e. on 30-Aug-16.

Tue 07 Jun 2016

Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

By |Tuesday, June 07th, 2016|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 42 – Split a Sentence Into Words in Different Cells

Enter following formula in B1 and drag to the right and down.

=IFERROR(MID($A1,FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A))),
FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:B)))-1-
FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A)))),"")

The workbook containing this solution can be downloaded from Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

Sat 07 May 2016

Challenge 42 - Split a Sentence Into Words in Different Cells

By |Saturday, May 07th, 2016|Categories: Challenges|Tags: , , , , , , , , |0 Comments

This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words.

Remember, it has to be a single formula which should be put into B1 and dragged to the right.

See the examples below -

1

A workbook containing this challenge can be downloaded from Challenge 42 - Split a Sentence Into Words in Different Cells

The solution to above challenge will be published on 7-Jun-16.