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.

Sun 27 Mar 2016

Solution - Challenge 37 – Find nth Alphabet from the Bottom

By |Sunday, March 27th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 37 – Find nth Alphabet from the Bottom.

Put following formula as Array formula in E1

=IF(SUM(--(ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13))>=C1,
INDEX(A1:A100,LARGE((ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13)
*ROW(A1:A100),C1)),"")

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the solution can be downloaded from Solution - Challenge 37 - nth Alphabet from the Bottom.

Sat 27 Feb 2016

Challenge 37 - Find nth Alphabet from the Bottom

By |Saturday, February 27th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

Suppose, you have been given a layout as below. The challenge before you is to write a formula to find nth alphabet from bottom. If no criterion is met, blanks should be returned.

C1 hold the value of that nth.

For C1 = 5, answer would be k.
for C1 = 10, answer would be blanks.

The Excel sheet related to this can be downloaded from Challenge 37 - nth Alphabet from the Bottom

1

The solution to above challenge will be published after a month i.e. on 27-Mar-16.

Sat 14 Nov 2015

Tips & Tricks 117 - VBA - How to Count a particular character in a String

By |Saturday, November 14th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string -

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be 5.

To count the same in a range of cells -

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

Now, how to do the same thing in VBA. In VBA, you can use following expression to count this -

UBound(Split(LCase(Range("A1")), "a"))

To count the same in a range of cells -

UBound(Split(LCase(Join(WorksheetFunction.Transpose(Range("A1:A10")))), "a"))

Mon 09 Nov 2015

Solution - Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters

By |Monday, November 09th, 2015|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters.

The formula for counting number of alphabets

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),"")))

The formula for counting number of numerals

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

The formula for counting other characters

=LEN(SUBSTITUTE(A1," ",""))-C1-C2

Where C and C2 are number of alphabets and numerals.

Sat 10 Oct 2015

Challenge 27 - Count the Number of Alphabets and Numerals and Other Characters

By |Saturday, October 10th, 2015|Categories: Challenges|Tags: , , , |1 Comment

Suppose, you have been given a string in A1 = "234, Washington D. C. @ 45609"

The challenge before you is to write 2 formulas to count the number of

1. Alphabets
2. Numerals
3. Other Characters other than space

For example, in above string, number of alphabets = 12 and  number of numerals = 8 and Number of other Characters other than Space = 4.

Once again, all 3 need to be counted separately. Hence, you have to make 3 formulas - One for alphabets and one for numerals and one for other Characters other than Space.

We should be able to use the formula in Excel 2003 also, hence multiple SUBSTITUTES will not be working here.

You may choose to pose your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 09-Nov-15.

Sat 25 Jul 2015

Tips & Tricks 101 - Get Column Name for a Column Number

By |Saturday, July 25th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

Let's suppose, you have a number in A1 and you want to get the column Name for that.

Hence, if A1=1, you want "A"
Hence, if A1 =26, you want "Z"
Hence, if A1=27, you want "AA" and so on.

The formula to derive the column name would be be -

=SUBSTITUTE(ADDRESS(1,A1,4),1,"")

Sat 18 Oct 2014

Tips & Tricks 60 - Remove numbers from string

By |Saturday, October 18th, 2014|Categories: Tips and Tricks|Tags: , , , , , |1 Comment

To remove numbers from a string (for example Vij1aY A. V4er7ma8 contains numbers which are not required), we can use nested SUBSTITUTE function to remove numbers. Use below formula assuming string is in A1 cell -

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Note - Since this formula is in multiple lines, hence you will have to copy this in Formula Bar. If you copy this formula in a cell, it will copy this in three rows.

Sat 05 Jul 2014

Tips & Tricks 30 - Generate an Array of a to z

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

I have already talked about generating a to z in a sequence in below post.

Now, I want to generate an array {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v",
"w","x","y","z"} to use in a formula.

To generate Small or Capital letters, do it while generating the sequence itself.

Let's assume that a to z are generated in A1 to A26. Put a in A1 and drag down till A26. If you want to generate a to z in capital letter, put A in A1 and drag down.

In a cell, put following formula and press F9

= TRANSPOSE(A1:A26)

In the formula bar, you will have array generated which you can copy and paste and use in the formula.

1

Sat 05 Jul 2014

Tips & Tricks 29 - Generate Alphabets A to Z (or a to z)

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

For Capital A to Z

Put A in first cell and drag down.

If you want to do it through formula -

In Cell A1, put this formula and drag it till 26th row

=CHAR(65+ROW()-1) or CHAR(64+ROW())

CHAR(65) is A. You can verify this by CODE("A") formula which will generate 65.

If you are putting this formula is some other row apart from 1, adjust the mathematical expression within CHAR in such a manner that first entry is 65.

For example, if you are starting in row 10, the formula would become

= CHAR(65+ROW()-10) or CHAR(64+ROW()-9)

For Small a to z

Put a in first cell and drag down.

If you want to do it through formula -

Replace 65 with 97 or 64 with 96 in above formulas.