Hit enter after type your search item

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 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.

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:

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 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:

TEXT day name

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 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:

TEXT leading zeros

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:

TEXT thousand seperator

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:

TEXT Dates

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

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:

Excel TEXT time

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

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:

Excel TEXT CONCATENATE

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:

TEXT CONCATENATE 2

This div height required for enabling the sticky sidebar