Suppose you have a name like John Doe Smith in Cell A1. You want to extract the last name from the string. 1. Formula Way Put following formula =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) 2. Manual Way Manual way is very handy when you have a large data set. 1. Select your column 2. CTRL+H 3. In Find…
Author: Vijay A. Verma
Tips & Tricks 22 – Extract First Name from Full Name
Suppose you have a name John Doe Smith and you want to extract the first name only. Put following formula assuming full name is in Cell A1. Formula Way =LEFT(A1,FIND(" ",A1&" ")-1) Manual Way Manual way will be useful when you have a long list of names. A. Easy Way – Find and Replace Way…
Tips & Tricks 21 – Count only Alphabets in a String
Suppose you have a string "Ab?gh123def%h*" and you want to count only Aphabets. Suppose your string is in A1, put following formula for this. =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),""))) OR =SUMPRODUCT(–(ABS(77.5-CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13)) OR =SUMPRODUCT(–(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)= {"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"}))
Tips & Tricks 20 – Count Numbers in a String
Suppose you have a string "abc123def43cd" and you want to count numbers in this. If your string is in A1, use following formula – =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,""))) OR =SUMPRODUCT(–ISNUMBER((–MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
Tips & Tricks 19 – Count Non Numbers in a String
Suppose you have a string "abc123def45cd" and you want to count non numbers in this. If your string is in A1, use following formula in A1 =IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(–NOT(ISNUMBER((–MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))
Tips & Tricks 18 – Count No. of Unique Values
Use following formula to count no. of unique values – =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) Credit – There are various ways to accomplish above. I knew all other formulas. But the above formula is the sleekest. I learnt it from a posting to Excel Microsoft Community.
Tips & Tricks 17 – Count No. of Cells which are containing only Characters
Hence, if your cell is having a number 2.23, it will not be counted as it is a number. Use below formula considering your range is A1:A10 =COUNTIF(A1:A10,"*")
Tips & Tricks 16 – Count No. of Cells Having Numbers Only
COUNT function counts only those cells which are having numbers. Assuming your range is A1:A10, use following formula =COUNT(A1:A10)
Tips & Tricks 15 – Most Frequently Occurring Value in a Range
Assuming, your range is A1:A10, enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing 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 {…
Tips & Tricks 14 – Count Cells Starting (or Ending) with a particular String
1. Say you want to count all cells starting with C =COUNTIF(A1:A10,"c*") c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel. =COUNTIF(A1:A10,"excel*") 2. For ending =COUNTIF(A1:A10,"*c") c* is case insensitive. Hence, it will count cells starting with both c…