The TEXT function in Excel
The Excel TEXT function can be used to convert numbers and dates into text. This is particularly useful for scenarios where you require displaying the numbers or dates with text strings. For example:
The Excel TEXT function can be used to convert numbers and dates into text. This is particularly useful for scenarios where you require displaying the numbers or dates with text strings. For example:
The Sales for 10/01/2015 to 10/31/2015 is $25000.79.
Where dates and amount are kept in different cells and combined in a cell for better reading. You may also use TEXT function for formatting dates.
- 1. The Syntax of using TEXT function
- 2. A simple example of TEXT function
- 3. Display day name of today by TEXT function
- 4. Number with %, and two decimal points
- 5. Display leading zeros for a number example
- 6. Display thousand separators in numbers
- 7. The example of date formatting codes in TEXT function
- 8. Format codes for time in TEXT function
- 9. An example of using TEXT function with CONCATENATE
The Syntax of using TEXT function
The general syntax for using the TEXT function in Excel is:
=TEXT(Value to convert into text, “Formatting code “)
- In the first argument, you may provide a number, date etc. to be converted into the text.
- The formatting code like decimal points in numbers, date format etc. can be set there
By using formatting codes, you may format date and times as per the requirement and locale.
Note: The TEXT function is generally used in conjunction with other things e.g. CONCATENATE function, ‘&’ concatenation character etc. You will rarely require using this function by itself.
A simple example of TEXT function
Let me start with a basic example of using TEXT function. In the A2 cell, a number is given while a date is given in A3 cell. The TEXT function is used in B2 and B3 cells for converting the respective number and date into text:
The B2 formula:
The B3 formula:
The evidence that both are converted to text is the display of number and date in B2 and B3 cells as left aligned. By default, the number and date are aligned right as in the case of A2 and A3 cells.
Display day name of today by TEXT function
The following example used TODAY() function to get the current date and displayed the day name e.g. Tuesday, Wednesday by using TEXT function:
The TEXT formula:
The result:
You may also like to learn: Excel VBA today’s date
Number with %, and two decimal points
This example displays the specified A2 cell number with percentage and A3 cell with two decimal points with ‘$’ sign:
The formula for B2:
For B3:
The result:
Display leading zeros for a number example
By default, Excel will not display leading zeros for the numbers. For example, if you try entering a number 00525, the Excel displays it as 525.
The example below shows displaying the leading zeros for numbers in different cells.
The TEXT formula:
You may use more or less number of zeros – depending on the requirement.
Display thousand separators in numbers
This example displays the numbers with 1000 separators. You may use # or 0 in the formatting code as shown below:
The formula used in B2 for A2 number:
Formula in B3:
In B4:
The example of date formatting codes in TEXT function
The following example shows using a few date formatting codes with the TEXT function. For the example, I have used “12/20/2016” date and by using TEXT function formatting code argument, the month number, month name in three letters, full month name, the day with and without leading zero, day name in three letters and full day name are displayed as follows:
If you want to copy any of these formulas, these are listed below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
For Short Month: =TEXT(A4,"mmm") For Full Month Name: =TEXT(A4,"mmmm") Month Number (1-12): =TEXT(A4,"m") Month Number (01-12): =TEXT(A4,"mm") Short Day Name: =TEXT(A4,"ddd") Full Day Name: =TEXT(A4,"dddd") Day Number (01-31) : =TEXT(A4,"dd") Short Year (two digits): =TEXT(A4,"yy") Full Year (four digits): =TEXT(A4,"yyyy") |
Interested learning: Age formula in Excel
Format codes for time in TEXT function
Similarly, you may use the format code argument in TEXT function for displaying the time in your desired format. Like we used m, d, y variations for dates, the h, m, s, am/pm are used for time formatting.
The format code is case insensitive i.e. you may use h or H etc.
See the following example with various formulas used to display time:
The following formulas are used for getting the time, hours, minutes and seconds from the A4 cell (4:55 PM).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
For time in AM/PM: =TEXT(A4,"h AM/PM") Time in AM/PM: =TEXT(A4,"h:mm AM/PM") Time in 24 hours: =TEXT(A4,"h:mm:ss") For hours (0-23): =TEXT(A4,"h") Hours in 00-23: =TEXT(A4,"hh") Minutes (0-59): =TEXT(A4,"m") Minutes (00-59): =TEXT(A4,"mm") Seconds (0-59): =TEXT(A4,"s") Seconds (00-59): =TEXT(A4,"ss") |
An example of using TEXT function with CONCATENATE
The example below shows using the TEXT function with CONCATENATE function. For that, the date in A2 cell is converted to text and combined with a string by using the CONCATENATE function.
For showing the difference, the B2 displays the result by simply joining A2 date without TEXT function:
You see, the formatting of date in A2 cell is removed in B2 as I simply used it in CONCATENATE function. The C2 displayed it correctly where this formula is used:
The formula in B2:
See another formula for displaying this date more elegantly:
The formula:
The result:
