Text function and it's use in excel

Here is a new post to convert any data or value to text using the text function. TEXT function is one among the awesome function which helps users to change formate to text format in excel. Usually, the text function is being used for converting to date, time, etc.

TEXT function

Syntax: TEXT( value, format_text )

Value: the Value can be a cell reference or the data which you wants to convert to text formate.

Format_text: The formate to which you wants to convert from the present value.

Examples of some formate are below with syntax used to convert text formate.

• TEXT(1000.567,"$#,##0.00")

Currency with a thousand separator and 2 decimals, like $1,000.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/18

• TEXT(TODAY(),"DDDD")

Today’s day of the week, like Monday

• TEXT(NOW(),"H:MM AM/PM")

Current time, like 4:29PM

• 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

Common Error

#NAME?-This is returned from the Excel Text function, if you omit the quotation marks from around the format_text argument.

Solution: Add quotes around the formatting definition.

For more information, you can also refer to support.office.com