Text Functions (String Functions):
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.
Syntax :
=TEXT(Value you want to format, "Format code you want to apply")
NOTE:
The TEXT function will convert numbers to text, which may make it difficult to reference in later calculations. It’s best to keep your original value in one cell, then use the TEXT function in another cell. Then, if you need to build other formulas, always reference the original value and not the TEXT function result.
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.
Syntax :
=TEXT(Value you want to format, "Format code you want to apply")
Formula
|
Description
|
|---|---|
=TEXT(1234.567,"$#,##0.00")
|
Currency with a thousands separator and 2 decimals, like $1,234.57. Note that Excel rounds the value to 2 decimal places.
|
=TEXT(TODAY(),"MM/DD/YY")
|
Today’s date in MM/DD/YY format, like 03/14/12
|
=TEXT(TODAY(),"DDDD")
|
Today’s day of the week, like Monday
|
=TEXT(NOW(),"H:MM AM/PM")
|
Current time, like 1:29 PM
|
=TEXT(0.285,"0.0%")
|
Percentage, like 28.5%
|
=TEXT(4.34 ,"# ?/?")
|
Fraction, like 4 1/3
|
=TRIM(TEXT(0.34,"# ?/?"))
|
Fraction, like 1/3. Note this uses the TRIM function to remove the leading space with a decimal value.
|
=TEXT(12200000,"0.00E+00")
|
Scientific notation, like 1.22E+07
|
=TEXT(1234567898,"[<=9999999]###-####;(###) ###-####")
|
Special (Phone number), like (123) 456-7898
|
=TEXT(1234,"0000000")
|
Add leading zeros (0), like 0001234
|
=TEXT(123456,"##0° 00' 00''")
|
Custom - Latitude/Longitude
|
NOTE:
The TEXT function will convert numbers to text, which may make it difficult to reference in later calculations. It’s best to keep your original value in one cell, then use the TEXT function in another cell. Then, if you need to build other formulas, always reference the original value and not the TEXT function result.
Length function:
Return the no of characters in a text string.
Syntax: =Len (“Text”)
Example: =Len (“Karunakar Reddy”)
UPPER Function :
Convert a text string into all upper case letter.
Syntax : =Upper("string")
Example : =upper("faculty")
Lower Function :
Convert a text string into all lower case letters.
Syntax : =lower("string")
Example : =lower("SARVAVASI")
Left Function :
Returns the specified no of characters from the start of text string.
Syntax : =LEFT(text,[num_chars])
Example : =Left("love you my professional",22)
Right Function:
Returns the specified no of characters from the end of text string.
Syntax : =right("text",[num_chars])
Example : =right("love you my professional",12)
MID Function :
MID returns a specific number of characters from a text string,
starting at the position you specify, based on the number of
characters you specify.
The MID function syntax has the following arguments:
Text : The text string containing the characters you
want to extract.
Start_num: The position of the first character you want to extract
in text. The first character in text has start_num 1, and so on.
Num_chars : Specifies the number of characters you
want MID to return from text.
Syntax : =MID(text,start_num,num_chars)
Example : =MID("Microsoft Excel Functions",1,16)
Follow @Sarvavasi.com