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 08 Aug 2015

Tips & Tricks 103 - I need to fill in A-Z or a-z very often. What to do

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. Put =CHAR(96+ROW()) in cell A1.
2. Drag down till A26 to generate a to z.
3. Put =CHAR(64+ROW()) in cell B1.
4. Drag down till B26 to generate A to Z.
5. File > Options > Advanced > Go down till you find Edit Custom Lists (You have to go down till bottom) and click this

1

6. Now, you can import your range as below.

1

7. Now, any time you need this sequence just put a or A and drag down. The sequence will be generated.

Sat 11 Oct 2014

Tips & Tricks 58 - Remove Alphabets from a String

By |Saturday, October 11th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If your string is in cell A1, use following formula to remove all alphabets from a string

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(LOWER(A1),"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","")