In this post we will see how we could extract all the capital/uppercase letters from a cell in excel.
Below is an example where we have data in column A that has names that contains capital characters and we would like to get all the capital letters in the adjacent cell in column B.
Here in the data above we could see that we have capital letters in the initials of the names and also embedded between the characters in the name.
To get all the capital letters from a cell in excel, we will use SUBSTITUTE formula in excel.
Enter the formula in cell B1 as shown below in the figure and press enter.
=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(SUBSTITUTE(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”,””),” “,””)
As we could see, we have got capital letters from cell A1.
Now drag the formula to accommodate all data available in column A.
Hope this helped.
