TEXT Function in Excel

Explore eight practical examples of using the Excel TEXT function to convert numbers and dates to custom text formats.

Visual Tutorial: Explore the Power of Excel TEXT Function - Learn to Create Customized Text Formats for Numbers and Dates with this Comprehensive Infographic.

Excel TEXT Function

  • 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.
  • You may also use the TEXT function for formatting dates.
  • Generally, the TEXT function is used with other functions. For example,
=CONCATENATE("Event starting at ",TEXT(A4,"mm/dd/yyyy"))

Syntax of using the TEXT function

The general syntax for using the TEXT function in Excel:

=TEXT(Value to convert into text, "Formatting code")
Argument Description
Value to convert into text In the first argument, you may provide a number, date, etc. to be converted into the text.
Formatting code The formatting code like decimal points in numbers, date format, etc.

By using formatting codes, you may format dates 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 be required to use this function by itself.

A simple example of the 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 the 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:


Excel TEXT function

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 the day name of today by TEXT function

The following example used the TODAY() function to get the current date and displayed the day name e.g. Tuesday, Wednesday by using the TEXT function:

The TEXT formula:


The result:

TEXT day name

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:

TEXT number percentage

Display leading zeros for a number example

By default, Excel will not display leading zeros for the numbers. For example, if you try entering the number 00525, Excel displays it as 525.

The example below shows the leading zeros for numbers in different cells.

The TEXT formula:


TEXT leading zeros

You may use more or less number of zeros – depending on the requirement.

Display a thousand separators in numbers

This example displays the numbers with 1000 separators. You may use # or 0 in the formatting code as shown below:

TEXT thousand seperator

The formula used in B2 for A2 number:


The 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 the “12/20/2016” date and by using the 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:

TEXT Dates

If you want to copy any of these formulas, these are listed below:

Formula Description
=TEXT(A4,"mmm") Short Month (e.g., Jan, Feb)
=TEXT(A4,"mmmm") Full Month Name (e.g., January)
=TEXT(A4,"m") Month Number (1-12)
=TEXT(A4,"mm") Month Number (01-12)
=TEXT(A4,"ddd") Short Day Name (e.g., Mon, Tue)
=TEXT(A4,"dddd") Full Day Name (e.g., Monday)
=TEXT(A4,"dd") Day Number (01-31)
=TEXT(A4,"yy") Short Year (two digits)
=TEXT(A4,"yyyy") Full Year (four digits)
Interested learning: Age formula in Excel

Format codes for time in TEXT function

Similarly, you may use the format code argument in the TEXT function to display 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:

Excel TEXT time

The following formulas are used for getting the time, hours, minutes, and seconds from the A4 cell (4:55 PM).

Formula Description
=TEXT(A4,"h AM/PM") Time in AM/PM format (e.g., 3 PM)
=TEXT(A4,"h:mm AM/PM") Time in AM/PM format with minutes (e.g., 3:15 PM)
=TEXT(A4,"h:mm:ss") Time in 24-hour format with seconds (e.g., 15:30:45)
=TEXT(A4,"h") Hours (0-23)
=TEXT(A4,"hh") Hours in 00-23 format
=TEXT(A4,"m") Minutes (0-59)
=TEXT(A4,"mm") Minutes in 00-59 format
=TEXT(A4,"s") Seconds (0-59)
=TEXT(A4,"ss") Seconds in 00-59 format

An example of using TEXT function with CONCATENATE

The example below shows using the TEXT function with the CONCATENATE function. For that, the date in A2 cell is converted to text and combined with a string by using the CONCATENATE function.

To show the difference, the B2 displays the result by simply joining the A2 date without the TEXT function:


You see, the formatting of the 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:

=CONCATENATE("Event starting at  ",TEXT(A2,"mm/dd/yyyy"))

The formula in B2:

=CONCATENATE("Event starting at ",A2)

See another formula for displaying this date more elegantly:

The formula:

=CONCATENATE("Event starting at  ",TEXT(A2,"dddd d mmmm, yyyy"))

The result: