Breaking News
You are here: Home / Notes / SEM-1 / PC Software-I / MS EXCEL Text Functions

MS EXCEL Text Functions

Text function

Text functions are among the most widely used functions in Excel.

Function Description
CHAR() CHAR() function returns the character specified by the code number.
Syntax =CHAR(number)
Example =CHAR(65)=CHAR(33)
Output (1)  “A”(2)  “!”
CONCATENATE() Joins several text strings into one text string. 1 to 30 text strings to be joined into one single text items.
Syntax =CONCATENATE (text1, text2 …)
Example =CONCATENATE(“Mary”,”&”,”Contrary”)
Output Mary & Contrary
DOLLAR() Converts a number to text and displays that number in the standard currency format.
Syntax =DOLLAR(number, decimals)
Example = DOLLAR(12345.67,2)
Output $12,345.67
EXACT() Checks whether two text, strings are exactly the same and returns TRUE or FALSE.EXACT is a case sensitive.
Syntax = EXACT(text1,text2)
Example = EXACT(“ABC”,”ABC”)
Output True
FIND() Returns the starting position of one text string within another text string. FIND () is a case sensitive.
Syntax = FIND(find-text, within-text, start-num)
Example =FIND(“O”,”Hello,How are you?”,3)
Output 5
SEARCH() Returns the number of the character at which a specified character or text string is first found, reading left to right. SEARCH is not a case sensitive.NOTE: The difference between the 2 function is that FIND is a case sensitive while SEARCH is not a case sensitive. FIND can’t use wildcard characters like “?”,”*”,”~” etc. while SEARCH can use wildcard character.
Syntax = SEARCH(find-text, within-text, start-num)
Example =SEARCH(“E”,”Statements”,6)
Output 7
FIXED() Rounds a number to the specified number of decimals and return result a text with or without commas.
Syntax =FIXED(number,decimals’no commas)
Example =FIXED(12345.67,2)=FIXED(12345.67,-2)

=FIXED(-12345.67,2)

Output (1)  12345.67(2)  12,300

(3)  -12345.67

(4)  -12345.67

LEFT() Returns the specified number of characters from the start of a text string..
Syntax =LEFT(text,num-chars)
Example =LEFT(Sale price,4)
Output Sale
RIGHT() Returns the specified number of characters from the end of a text string.
Syntax =RIGHT(text,num-chars)
Example =RIGHT(Sale price,5)
Output Price
LEN() Returns the number of character in a text string.
Syntax =LEN(text)
Example =LEN(phone)
Output 5
LOWER() Converts all the upper case letters in a text string to lower case.
Syntax =LOWER(text)
Example =LOWER(“John D Smith”)
Output John d smith
UPPER() Converts all the lower case letters in a text string to upper case.
Syntax =UPPER(text)
Example =UPPER(“John D Smith”)
Output JOHN D SMITH
PROPER() Converts a text string to proper case, the first letter in each word in upper case and all the letters in lower case.
Syntax =PROPER(text)
Example =PROPER(“john D. Smith”)
Output John D. Smith
MID() Returns the character from the middle of a text string,given a starting position and length,
Syntax =MID(text,start-num,num-chars)
Example =MID(Fluid Flow,1,5)
Output Fluid
REPLACE() REPLACE is used to replace the text in a specified location.
Syntax =REPLACE(old-text,start-num,num-chars,new-text)
Example =REPLACE(2009,3,2,”10”)
Output 2010
SUBSTITUTE() Replace existing text with a new text in a text string. Substitute is used to replace a specified text string.
Syntax =SUBSTITUTE(text,old-text,nem-text,instance-num)
Example =SUBSTITUTE(Sales data,”Sales”,”Cost”)
Output Cost data
REPT() Repeats text a given number of times.
Syntax =REPT(text,number-times)
Example =REPT(“*-“,2)
Output *- *-
T() Checks whethwer the value is text and returns the text if is or returns double quotes(empty text) if it is not.
Syntax =T(value)
Example =T(Rainfall)
Output Rainfall
TEXT() Converts a value to text in a specific number format.
Syntax =TEXT(value,format-text)
Example =Buchanan and “Sold” and TEXT(2800,”$0.00”) and “Worth of units”=TEXT(12345.67,”# ?/?”)

=TEXT(12345.67,”m/d/yy h:mm”)

Output (1)  Buchanan sold $2800.00 worth of units.(2)  12345 2/3

(3)  10/18/33 16:04

TRIM() Remove all spaces from a text string except for single spaces between words
Syntax =TRIM(text)
Example =TRIM(“First Quarter Earnings”)
Output First Quarter Earnings
VALUE() Converts a text string that represents a number to a number.
Syntax =VALUE(text)
Example =VALUE(“$ 1,000”)
Output 1000

Download Paper Solutions from Below Link

About admin

Scroll To Top