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.
Syntax of TEXT function
- =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
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 |