The TEXT functions are listed under FORMULAS
TEXT.
We have explained about different TEXT functions like upper, lower, proper, trim, len, left, right, exact, clean, fixed, find, search, concatenate etc.
Note: in our explanation, we have mentioned first argument, second argument, third argument, fourth argument etc. First argument always starts from the left.
If you need to change the typed character to upper case, you can use UPPER function. If you want to change John to JOHN. In any cell, type =; select upper from text functions; in the new screen, click text box; then click b5 cell, and click okay. You can see JOHN now
Example: =UPPER(John) would return JOHN
Similarly if you need to change the typed character to lower case, you can LOWER function.
Example: =LOWER (John) would return john
Similarly if you want to change the typed character to PROPER (first character upper and the other characters lower), you can PROPER function.
Example: =PROPER (john smith) would return John Smith
Working in real time, you get data with spaces. Example ( Vi vi an Rich ar ds; Actual name is Vivian Richards) You can use TRIM function to remove spaces in the words Vivian and Richards. It will not separate the spaces between words.
Example: =TRIM (Vi vi an Rich ar ds) would return Vivian Richards
Counts the number of strings in that cell.
Example: =LEN (JOHN) would return 4
Left function has two arguments (Text, Num_Chars).
Text (First argument): You have to mention the text on which you are searching.
Num_Chars (Second Argument): You have to mention a numeric value, which will extract characters from the text mentioned in the first argument and display those characters. The number of extracted characters will be from the left and equal to that numeric value typed in the second argument.
Example:
=LEFT ("Information Technology", 5) would return Infor
=LEFT ("Information Technology", 14) would return Information Te
Right function has two arguments (Text, Num_Chars).
Text (First argument): You have to mention the text on which you are searching.
Num_Chars (Second Argument): You have to mention a numeric value, which will extract characters from the text mentioned in the first argument and display those characters. The number of extracted characters will be from the right and equal to that numeric value typed in the second argument.
Example:
=RIGHT ("Information Technology", 5) would return ology
=RIGHT ("Information Technology", 14) would return ion Technology
It is a case sensitive function and compares whether two arguments are same.
Text1 (First Argument): You have to mention the text
Text2 (Second Argument) you have to mention the text
Example:
=EXACT("one","one") would return TRUE
=EXACT("One","one") would return FALSE
When you import data into excel, you may get unwanted characters, which may not be seen by your eyes. To remove those unprintable characters, you use Clean function.
Fixed function has 3 arguments (Number, Decimals, and No_Commas)
Number (First Argument): A numerical value that has to be rounded.
Decimals (Second Argument): Number of digits to the right of the decimal point.
No_Commas (Third Argument): Whether you need COMMAS or not. If you specify TRUE, commas will not be there. If you specify FALSE, you can see commas.
Example:
=FIXED(123456.5566,2,TRUE) would return 123456.66
=FIXED(123456.5566,2,FALSE) would return 123,456.56
Find function has 3 arguments (Find_Text, Within_Text, Start_Num). Find Function is case sensitive.
Find_Text(First Argument): You have to mention the TEXT that you are searching.
Within_Text(Second Argument). You have to mention the TEXT on which you are searching.
Start_Num(Third argument): You have to specify the number of that character from which you want to search.
.Example:
=FIND("for", "Information Technology",2) would return 3 since for starts from the third character
=FIND("nolo","Information Technology",1) would return 17 since nolo starts from the 17th character.
=FIND("NOLO","Information Technology",1) would not return anything since NOLO is in upper case and you have nolo in the text.
Search function is similar like find function but it is not case sensitive. It can accept wild cards also.
Replace function has 4 arguments (Old_Text, Start_Num, Num_Chars_New_Text).
Old_Text (First Argument): you have to mention the text which has to be replaced.
Start_Num(Second Argument): You have to specify the number of that character from which you want to replace.
Num_Chars (Third Argument): The number of characters to be replaced in Old_Text(First Argument)
New_Text (Fourth Argument): The new text that will be replaced in Old_Text(First Argument)
Example:
=REPLACE("Information Technology",1,3,"AAA") would return AAAormation Technology.
You have to refer the Excel_Exercise diagram:
Lookup Functions in Excel 2013