Tips & Tricks 163 - Convert Alphabets to Numbers
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
Sep 2017
Sat 30
Jul 2016
Challenge 48 - Whether a Sentence is Pangram or Not
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
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
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 -
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.