Hello friends, there are lot of data formats during creating the reports in MS Excel. Text data type play an important role during preparing reports in Excel. Here we have to discuss few important text Functions of Microsoft Excel.
do you known about text functions in MS Excel? These text functions are very important to prepare MIS reports during doing job in any company, preparing financial report, prepare employees database in your company and many more as per the company requirement. So, today we discuss about most important text functions of Microsoft Excel which is used by the lot of persons during doing daily official jobs in the Microsoft Excel sheet. Let’s have a look and give your proper attention on this post.
Important Text Functions of Microsoft Excel
(1) LEFT () Function:
It is one of the most important function which is used in the Text category in Microsoft Excel. LEFT function is used to extract amount of char(s) of start from the string of text.
Syntax: LEFT(text, [num_chars])
Text: It is the text string that has number of characters in which you want to extract.
num_chars: It specifies the number of characters you want to extract from left most side.
Example: If you type “Knowledge Always Help Us” in the cell A1.
Now, type the following formula to extract “Knowledge” from left most side of given text string, you have to apply this given formula:
= LEFT(A1,9)
When you press enter key Excel displays “Knowledge” word as a result.
Must Read: How to use Nested “IF” function with OR function in MS Excel
(2) RIGHT () Function:
It is one of the most important function which is used in the Text category in Microsoft Excel. RIGHT function is used to extract number of characters from the right most side of given text string.
Syntax: RIGHT(text, [num_chars])
Text: It is the text string that has number of characters in which you want to extract.
num_chars: It specifies the number of characters you want to extract from left most side.
Example:
If you type “Knowledge Always Help Us” in the cell A1.
Now, type the following formula to extract “Help Us” from right most side of given text string, you have to apply this given formula:
= RIGHT(A1,7)
When you press enter key Excel displays “Help Us” word as a result.
(3) MID () Function:
It is one of the most important function which is used in the Text category in Microsoft Excel. MID function is used to extract number of char(s) from center place from the provided text string, and also give a initial location and span.
Syntax: MID(text, starting number, number of characters)
Text: It is the text string that has number of characters in which you want to extract.
Start_num: It is the point of the first character which you want to pull out in the given text string.
Num_chars: It is the number of characters which you want to extract from Middle from the given text string.
Example: If you type “Knowledge Always Help Us” in the cell A1.
Now, type the following formula to extract “Always” from the middle part of given text string, you have to apply this given formula:
= MID(A1,10,6)
When you press enter key Excel displays “Always” word as a result.
(4) CONCATENATE () Function:
It is one of the most important function which is used in the Text category in Microsoft Excel. CONCATENATE function is used to combine or joins given two or more text strings and displays the result into one text string.
Syntax: CONCATENATE (text1,text2,…)
Text1, text2, … : Text values are between 2 – 255 text items to be joined into a single text item. The text items may be any text strings, any numbers or any cell address.
Example:
If you type “Knowledge” in cell A3, “Always” in cell A4 and “Help Us” in the cell A5. Now, you want to combine all these given text string into a single text value then you have to apply this given formula:
=CONCATENATE(A3,A4,A5)
When you press enter key Excel displays “Knowledge Always Help Us” as a result.
Must Read: Trick to split active worksheet in several pages in MS Excel
(5) SUBSTITUTE () Function:
It is one of the most important function which is used in the Text category in Microsoft Excel. SUBSTITUTE function is used to replace any existing text string with another new text in a given text string.
Syntax: SUBSTITUTE(text,old_text,new_text,instance_numbers)
Text: It is the text value or any cell address that containing any text which you want to replace or change number of char(s).
Old_text: It is the text which you want to substitute or replace.
New_text: It is the text value which you want to change with the aged text.
Instance_num: It specifies which amount of aged text you want to change.
Example: If you type “Knowledge Always Help Us” in the cell A1. Now, you want to replace “Knowledge” word with “Skyneel” word then you have to apply this given formula:
=SUBSTITUTE(A1,”Knowledge”,”Skyneel”,1)
When you press enter key Excel displays “Skyneel Always Help Us” as a result.
I hope you like this Important Text Functions post, give your important suggestions in the comment box and also share your knowledge with your friends, colleagues, family members and others. Thanks.