=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","")
46. Remove numbers from string.................................................................................................................
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.
47. Roman Representation of Numbers
Use ROMAN function.
Hence ROMAN(56) will give LVI.
ROMAN works only for numbers 1 to 3999.
48. Sum Bottom N Values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up bottom N values
=SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10)))
In case, you want to ignore 0 values (and blanks)
=SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))
Both the above formulas will function only if there are at least N values as per ROW(1:N).
Hence, for above formulas, it would work only if there are at least 10 numbers in A1 to
A100.
To overcome this limitation -
Enter the below formulas as Array Formula
=SUM(IFERROR(SMALL($A$1:$A$100,ROW(1:10)),0))
