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

    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