Text Functions In Excel – MS Excel Functions Tutorial Part 4

In previous post we learnt mathematical functions in excel. In this post, we will learn Text Functions in Excel.

We will explore most useful text functions in excel in this post.

Function # 1 LEN

This function returns the length of a string. For e.g. let us consider a string “Excel”.

LEN(Excel) will return 5 as Excel contains 5 characters.

Function # 2 FIND

This function is used for finding the position of a specified text within a string. FIND is a case sensitive function and counts spaces as characters as well. For e.g. let us consider a string “Learning Excel”.

FIND(“a”, “Learning Excel”) will return the value 3 as “a” is the third character in the string.

Function # 3 SEARCH

This function is similar to the FIND function except that this is not case sensitive.

Function # 4 MID

This function can be used for extracting any number of characters from a string. Let us suppose that we a string “I like Excel” in cell D5. Let us now use MID.

=MID(D5,3,4)

This formula will tell excel to start at the third position in the string at cell D5 and extract 4 characters from there. The character at the third position is “l” and extraction of 4 characters starting from “l” results in “like”. Thus this formula will return “like”.
Note: Blank spaces are also counted as characters.

Function # 5 RIGHT

This function is used for extracting the rightmost characters from a string. For e.g let us consider a string “I love vacations” in cell F5. RIGHT(F5,3) will return “ons” since this formula tells excel to extract the 3 rightmost characters in the string.

Function # 6 LEFT

This function is similar to the RIGHT function except that this extracts the leftmost characters from a string.

Function # 7 REPLACE

This function is used for replacing a string with some other string. Let us consider a string “I am an engineer” in cell H5. Let us replace “engineer” by “indian”.

=REPLACE(H5,9,8, “indian”) tells excel to start at the 9th position and replace 8 characters from there by new text. The result is “I am an Indian”. This formula counts blank spaces as well.

Function # 8 SUBSTITUTE

This formula is similar to the REPLACE function except that it is used to replace a text with another text. REPLACE is used for replacing a whole string with a new string. For example let us suppose that we have a text “Peter” in cell J5 and we are interested in replacing “P” with “M” and making it “Meter.

=SUBSTITUTE (J5,”P”,”M”)

This formula will return the text “Meter”.

So we learnt how to use Text Functions in Excel and in next post we will learn Cleanup and Case Functions in Excel.

  1. I precisely deeirsd to thank you very much once again. I’m not certain the things I would have handled without the actual aspects shown by you on this subject. It had been a alarming case for me, nevertheless witnessing a new specialised avenue you managed that took me to leap over contentment. I’m just happy for the advice and in addition hope you recognize what a great job you are carrying out teaching the mediocre ones thru your blog post. Most likely you have never come across all of us.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">