Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

TEXT function: Description, Usage, Syntax, Examples and Explanation

What is TEXT function in Excel?

TEXT function is one of Microsoft Office Excel TEXT functions that 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.

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.

Worked Example:   Convert date to Julian format in Excel

Syntax of TEXT function

In its simplest form, the TEXT function says:

  • =TEXT(Value you want to format, “Format code you want to apply”)

Examples of TEXT function

Steps to follow:

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Worked Example:   Get day name from date in Excel

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

Take Note of the format codes within quotation marks.

Formula Description
=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(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(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(1234,”0000000″) Add leading zeros (0), like 0001234
=TEXT(1234567898,”[<=9999999]###-####;(###) ###-####”) Special (Phone number), like (123) 456-7898
=TEXT(123456,”##0° 00′ 00””) Custom – Latitude/Longitude
Worked Example:   Convert Numbers to Text in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *