Amazing Excel Formulas

We know that excel can find length of cell using LEN () function. How about finding Length of letters/Numbers/special characters only? To find the length of capital letters and small letters try the below formula, where A1 is your data =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{1,"N",2,"N"}))) Length of Capital letters only =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{1,"N","N","N"}))) Length of small letters only =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{"N","N",2,"N"}))) Length of numbers used in cell =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{48,58},{1,"N"}))) Length of cell apart from letters and numbers which is Special characters =LEN(A1)-SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{48,58,65,91,97,123},{1,"N",1,"N...

I was asked question about adding suffix like 1 st June 2014 2 nd June 2014   3 rd June 2014 etc Suppose Cell A1 is the date then try the below formula to get ordinal suffix =TEXT(A1,"d")&IFERROR(MID("1st2nd3rd21st22nd23rd31st",FIND(DAY(A1),"1st2nd3rd21st22nd23rd31st")+IF(LEN(DAY(A1))=1,1,2),2),"th")& TEXT(A1," mmm yyyy")