Microsoft Excel provides T and TEXT functions to display the text and format the numeric value, respectively. We will discuss about these functions in this Article.
The T function
This function is used to return the text from the given value. Truly speaking, this doesn’t convert the value to the text; instead it returns the text value from the given value. Confused? Here is an example:
| Formula | Result |
| =T(“Excel”) | Excel |
| =T(“123”) | 123 |
| =T(456) | |
| =T(TRUE) |
Observe that, this function returns text when you pass the text value. Otherwise, it returns an empty value. From above examples, when we pass an integer value 456, it returns an empty value. Also when we pass a logical value TRUE, it returns an empty value. That means, it simply displays the text value of the given value. If we pass non-text value, it simply return an empty value.
It is important to note that, when you pass a value within in double quotes (“); Excel treats it as a text value and hence this function returns the same text value. You can see this in the first two formulas in the above examples.
The TEXT function
Another important function Microsoft Excel provides is, the TEXT function. This function is used to format the given value (or number) to display in more readable form. It is important to note that, this function converts the numeric value to text.
The Syntax of the function is:
=TEXT(value, "format")
Where value is the numeric value to format. And format is the, format string; contains the format codes to apply to format the given value. The format argument should be a string containing format codes. Ensure to pass the valid format codes; otherwise, TEXT function returns a #VALUE! Error. Here is examples to understand more on this function:
| Formula | Result |
| =TEXT(12345, “MM/DD/YYYY”) | 10/18/1933 |
| =TEXT(TODAY(), “YYYY/MM/DD”) | 2018/12/25 |
| =TEXT(NOW(), “HH:MM”) | 13:54 |
| =TEXT(NOW(), “HH:MM AM/PM”) | 01:54 PM |
| =TEXT(123, “00000#”) | 000123 |
| =TEXT(456, “Banana”) | #VALUE! |
| =TEXT(1234567, “#,###.00”) | 1,234,567.00 |
Note that, from above examples; we have used some of the supported format codes. Let’s discuss about them:
- M, D and Y codes are used to represent the date format.
- H, M are used to mention the time in hours and minutes. We can use, AM/PM to represent 12-hour format.
- Represent the Number using # sign. And comma (,) can be used for thousand separator.
- We can use 0s (zeros) to add leading zeros before the number.
We will discuss more topics as we go.
🙂 Sahida
One thought on “Microsoft Excel – Text Functions – T and TEXT formulas”