Following are 10 different methods to create a Number Series (1, 2, 3,… 49, 50) in Excel. Excel Fill Handle to create Serial Numbers To create a number series like 5, 6, 7,… up to the number14, type 5 into a cell > 6 into the cell just below it > select these two cells…
Let’s see how to combine the SUMPRODUCT and TEXT functions in Excel to find out the number of birthdays in a particular month. Formula We have a list of Date of Births in the cells C2:C17. The following formula will return the number of birthdays in the month of July. =SUMPRODUCT(–(TEXT($C$2:$C$17,”MMMM”)=”July”)) Explanation SUMPRODUCT functions multiplies…
The following is the generic formula to extract the first name from a full name. =LEFT(“Full Name”,SEARCH(” “,”Full Name”)-1) Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in the cell C3, SEARCH function will return the position of ” ”…
A User Defined Function (UDF) which can split data into multiple Rows or Columns on the basis of the specified delimiter in Excel is explained in this blog post. Watch the video on Excel Formula to split data into Columns or Rows Text to Columns feature is the most commonly used Tool to split data…