In previous post we learnt mathematical functions in excel. In this post, we will learn Text Functions in Excel.
- Introduction To Excel Formulas & Functions
- Inserting Formula, Function & Named Range In Excel
- Mathematical Functions In Excel
- Text Functions In Excel
- Cleanup & Case Functions In Excel
- Logical Functions In Excel
- Lookup Functions In Excel
- Date & Time Functions In Excel
- Max & Min 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.
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.
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.