How to find character in text using excel formula ?

You can use below formula to find the character in any word.

e.g. cell B1 contains the text Marks with 50% and we need to find if it contains %, we can write the below formula - 
=MID(B1,FIND("%",B1,1),1)

Output of the above formula will be %.


You can further customize this formula to write Found -
=IF(MID(B1,FIND("%",B1,1),1)="%","Found")

Output of the above formula will be Found.


If there is no "%" in cell B1 the formula will return #value! error, to overcome this we can customize the formula as below -
=IFERROR(IF(MID(B1,FIND("%",B1,1),1)="%","Found"),"Not Found")

If the character is missing in cell B1 then the output of the above formula will be Not Found.


You can replace the % to any character you need to find and you will get the output accordingly.

Done....

Find the difference between the two dates in excel

Below is the inbuilt function in excel which will retrieve the difference between two dates. We can get the results in number of days, number of months or number of years.

We can take a simple example to find the age difference from date of birth till today -

Date of Birth    - 23/01/1984    (enter value in cell A1)
Today's Date   - 06/05/2014    (enter value in cell B1)

=DATEDIF(L2,M2,"Y")&" Years, "&DATEDIF(L2,M2,"YM")&" Months, and "&DATEDIF(L2,M2,"MD")&" Days"

The output of the above formula will be -
30 Years, 4 Months, and 13 Days



=DateDif(A1,B1,"Y") = output will be number of years (Y)
=DateDif(A1,B1,"M") = output will be number of months (M)
=DateDif(A1,B1,"D") = output will be number of days (D)
=DateDif(A1,B1,"YM") = output will be number of calendar months from start year, here the months will be for current year
=DateDif(A1,B1,"YD") = output will be number of calendar days from start year, here the days will be for current year
=DateDif(A1,B1,"MD") = output will be number of calendar days from start month, here the days will be for current month


Above combination of codes can be used at different situations, one example we saw above -
1. To find the Age
2. Remaining days to your wedding
3. Days remaining to Expiring warranty or maintenance
4. Total employment experience
5. Property Agreement going to expire
...... and more

Done....

*all codes in this blog are tested on Excel 2010