Posts

Showing posts from October, 2018

Practical Text Manipulation using MID LEFT RIGHT FIND Functions in MS Excel

Image
Text manipulation is one of the great features of MS Excel. It has a lot of built-in functions to extract desired values from a text. By using different combinations of these functions we can create a complex formula. I have listed a few practical examples which are very useful. Extracting a Door number from an address text.  8314 W. Galvin Lane West Des Moines, IA 50265 68 4th Ave. Neptune, NJ 07753 8 Oklahoma St. Ladson, SC 29456 I will use the LEFT and FIND functions to extract the Door number of an address.  =LEFT(B4,FIND(" ",B4)) Here I am using the delimiter space (" "). The formula first finds a space character which appears after the door number. Then extracts the value whatever if at the left side of the first space.  Extracting a ZIP code from an address  Generally, the ZIP code will at the end of the address. Using the RIGHT function, we can extract the ZIP code  =RIGHT(B9,5) ...

Practical examples on MS Excel date functions

Image
MS Excel has an amazing set of Date and Time functions. By tweaking these functions, we can create a complex and useful formula. I am trying to list down a few practical examples particularly on the Date part, which will be very useful for a moderate to advanced Excel user.  How to find the end of the month for a given date Note that  EOMONTH  will return a serial number. You have to change the format to date format.  =EOMONTH(TODAY(),0) =EOMONTH(DATE(2018,10,10),0) How to find the number of days remaining in a month This is useful for interest calculation on first and last month on any type of loan repayment schedule.  Use DAYS , EOMONTH,  and DATE functions. =DAYS(EOMONTH(TODAY(),0),DATE(2018,10,10)) Number of days remaining in this month or current month =DAYS(EOMONTH(TODAY(),0),TODAY()) Find the number of months between two dates In other words, find the date difference in terms of months. DATEDIF is one of the very ...