For Capital A to Z Put A in first cell and drag down. If you want to do it through formula – In Cell A1, put this formula and drag it till 26th row =CHAR(65+ROW()-1) or CHAR(64+ROW()) CHAR(65) is A. You can verify this by CODE("A") formula which will generate 65. If you are putting…
Tips & Tricks 28 – First Day of the Month for a Given Date
Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate the first day of the Current Month. Hence, you want to achieve a result of 10/1/2014 (MM/DD/YY). The formulas to be used – =DATE(YEAR(A1),MONTH(A1),1) =A1-DAY(A1)+1 =EOMONTH(A1,-1)+1
Tips & Tricks 27 – Find the Merged Cells
Sometimes, you would be required to find merged cells in Excel. One of the common cases is when you try to sort a column and all of a sudden of you see the pop-up regarding merged cell. You can do following to find merged cells – 1. Select the column. 2. CTRL+F and click Options….
Tips & Tricks 26 – Fill in the data from non blank cells to blank cells down
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…
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))))