Suppose you have data like below and you want to fill in blank rows with data with non blank row up. Here, in range A3:A5, you want value A to appear, in range B7:B9, you want value B to appear and so on. You need this kind of situation, mostly, when you are making pivot…
Category: Tips and Tricks
Tips & Tricks 25 – Extract Integer and Decimal Portion of a Number
To extract Integer portion, one of the below can be used – =INT(A1) =TRUNC(A1) Positive value in A1 – If A1 contains 84.65, then answer would be 84. Negative value in A1 – If A1 contains -24.39, then answer would be -24. If you want only +ve value whether value in A1 is -ve or…
Tips & Tricks 24 – Extract the Initial of Middle Name
Suppose, you have a name John Doe Smith and you want to show D as middle initial. Assuming, your data is in A1, you may use following formula =IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"") If name is of 2 or 1 words, the result will be blank. This works on 3 words name only as middle can…
Tips & Tricks 23 – Extract Last Name from Full Name
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…
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,"*")