Find day of a Date:

If you would like to find the day of a given date we can find it very easily, please look into the below example and formula,





In the above example, we have a date in cell A1 and we did find the day of that date on which it occurred. We used text function to find it, the formula convetedthe format of the date as “ddddddd”, then we got the day of the given date.

Replace a particular character in a text:

If you would like to replace a character in a text that exists in a particular position, we can do that please have a look at the below formula and example,



In the above example, we have the name “Jase” in the cell A1 and we had replaced the 2nd character with “o” and we convrted the name “Jase” as “Jose”.

Extracting a text in simple way:

We can extract a word or a particular text from a line in a simple way using the below referred formula.





In the above example, we have name of persons in the cell A1 to A3 and we have extracted the name of persons in the cell B1 to B3. We have used =SUBSTITUTE(A1,"Last Name: ","") formula to do that. This formula substitutes the text “Last Name: ” with a null value “”.

Extracting a type of text in a range of cells:

In case, if you have a same kind of data in a range of cells and you would like extract a particular data from that range. We can do that, please look into the below example and formula,




n the above example, we have name of persons in the cell A1 to A3 and they are not in the same length. We have extracted the name of persons in the cell C1 to C3. We have used =MID(A1,12,LEN(A1)-11) formula to do that. The first 11 characters in A1 is “Last Name: ” and we do not know the length of name of the person, since we can’t predict the length of name of the each person.

We had counted the number of characters in cell A1 and we subtracted that 11 characters which builds the first part (Last Name: ) of the sentence and now we got the number of character exist in the name of the person.

The formula above, will extract the characters from 12th position and up to the length of name of the individual.

Extracting a text in a sentence:

If you would like extract a particular word from a sentence or from a line. We can do that in excel, please look into the below examples and formulas.




In the above example we have name of person in cell A1 in the order of Last Name and First Name and we had extracted the last name “David” alone using =MID(A1,12,5) formula. The first 12 characters build the word “Last Name: ” and the next 5 characters contains the last name. The formula extracted five characters (David) after 12 characters from left.

Convert a date into text in date format:

If you would like to convert a date value into text, but with the date format. We can convert a date into text in a format as per our wish. Like MM/DDD/YYYY or MM-DDD-YYYY or DD-MMM-YY.




In the above example, we have dates in the A column and it was converted into text in a different format using TEXT formula.

Merging two cells in a simple way

If you would like to merge cells, without using CONCATENATE formula then we have a simple to do that. Below is the formula and example for that.




In the above example, we have first name and last name of a person in cells A1 and B1. We merged the two names in C1 using & symbol. The formula took the value in A1 and, it just merged it with the value in B1 and we had inserted a space between the two names using “ “. Like wise you can merge N number cells into a cell.