Text Functions in Excel 2013
Text Functions in Excel 2013
Text functions are applied on text, string or character. Excel provided some to manipulate the text in cells in various ways. Following are some common and important text functions :
Concatenate() – This function return text after joining of the given number of text as a argument in the function.
Syntax: =concatenate(text1, text2, text3,....)
Example: =Concatenate(“Suresh”, “ Kumar”) Ans. Suresh Kumar
=Concatenate(a2, “ ”, b2)
Left() – This function return characters from
the left side of the string according to specified number given in argument.
Syntax: =Left(Text, [num_char])
Example: =left(“Suresh”, 3) Ans. Sur
Right() – This function return characters from the right side of the given string according to specified number given in argument.
Syntax: =Right(Text, [num_char])
Example: =Right(“Suresh”,2) Ans. sh
Upper() – This function returns text after converting of specified string in Uppercase.
Syntax: =Upper(Text)
Example: =Upper("suresh") Ans. SURESH
Lower()- This function returns text in Lowercase of the given text as a argument.
Syntax: =Lower(Text)
Example: =Lower("SURESH") Ans. suresh
Proper() – This function returns the text in proper case, means first letter of the sentence in uppercase and rest of all is in lowercase.
Syntax: =Proper(Text)
Example: =Proper("SURESH") Ans. Suresh
Find() – This function returns the text position no. of specified finding text from given text. This function is case-sensitive.
Syntax: =Find(find_Text, within_text, [start _num])
Example: =Find(“e”, “Suresh”,1) Ans. 4
Search() – This function returns the text position after searching the specified character from given text. This is not a case-sensitive, this function support any wildcard character (* or ?).
Syntax: =Search(find_Text, within_text, [start _num])
Example: =Search(“e”, “Suresh”,1) Ans. 4
Trim() – This function returns the text after erasing all the unnecessary space from specified text given as an argument.
Syntax: =Trim(Text)
Example: =Trim(" Suresh Kumar ") Ans. Suresh Kumar
Exact() – This function returns TRUE or FALSE after comparison of specified two string. This function is case-sensitive.Syntax: =Exact(Text1, Text2)
Example: =Exact(“Ram”, “RAM”) Ans. False
Mid()- This function returns character from the string according to specified number from starting text and its length.
Syntax: =Mid(Text, start_text_num, num_char)
Example: =Mid(“Ram”,2,2) Ans. am
Rept() – This function repeats the text in specified number of times.
Syntax: =Rept(Text, num_char)
Example: =Rept(“*” , 8) Ans. ********
Replace() – This function replace the specified part of text with a new text.
Syntax: =Replace(old_text, start_num, num_chars, new_text)
Example: =Replace(“abcdefghijk”,6,5, “-”) Ans. abcde-k
Substitute() – This function replace the existing text with new text. This function is case-sensitive.
Syntax: =Substitute(text, old_text, new_text, [instance_num])
Example: =substitute(“Rama”, “a”, “o”, 1) Ans. Roma
=substitute(“Rama”, “a”, “u”, 2) Ans. Ramu
Len() – This function returns the length (number of character in the text) of string specified as an argument.
Syntax: =Len(Text)
Example: =Len(“Rama”) Ans. 4
Fixed() – This function returns as a text after rounding the number to the specified number of decimal point and with comma or without comma also specified in the argument of function.
Syntax: Fixed(Number, [decimal],[no_comma])
Example:
Dollar() – This function returns the text of the given as currency format.
Syntax: =dollar(Number, [decimal])
Example: =dollar(5432.6, 2) Ans. $ 5,432.60
Code() – This function returns the numeric value of the first character of the given text as argument.
Syntax: =code(Text)
Example: =code("Ant") Ans. 65
<Char() – This function return a character according to the given number. Number must be given between 1 to 255.
Syntax: =Char(Text)
Example: =Char(65) Ans. A
ASCII(American
standard Code for Information Interchange) code
65-90 à A-Z, 97-122 à a-z 48-57 à 0-9
Value() – This function return in number format
of given any type of constant number, date, time or currency format as a text
in argument.
Syntax: =Value(Text)
Example: =value(“$ 1,541.80”) Ans. 1541.8
0 comment(s)