Introduction

What is Excel TEXT function?

Excel TEXT function is designed to convert any non-text values on the worksheet – e.g., numbers, dates, currencies, percentages – to a text format specified by the user.

Why do you need TEXT function?

TEXT function is widely used in a broad range of situations when you need to make the data on the worksheet presentable to the user rather than perform additional calculations on it. Just a few illustrations:

  • Including a number or a date in a string of text. In this situation, by default, a number will be displayed with all digits after decimal point, and a date will show up as a serial number value. TEXT function will help quickly convert them both to a readable format
  • Displaying a number or date in a user-defined format. Note that you will not be able to perform any calculations on this new number or a date anymore. This is why we would rather recommend using custom number formats for these purposes
Quick example
text function example

Figure 1. TEXT function example

Sample task

Let’s say we have sales data for the last month and their exact timing. We pull these numbers from other parts of the workbook, and they are automatically updated each month. We need a simple string of text summarizing sales data in a readable format. This text string can then be easily copied to our Word report or displayed in the output worksheet of our Excel file.

Figure 1 shows an example of how the final result can look like.

How to do it?

Here is how to create the summary text string described above (see Figure 2):

  1. Select an output cell (B5 in this case)

    text function formula

    Figure 2. Text function formula

  2. Type =”Sales in “&TEXT(
  3. Select a cell with the date (C2 in this case)
  4. Type ,”MMMM YYYY”)&”: “&TEXT(
  5. Select a cell with the sales number (C3 in this case)
  6. Type ,”$#,##0”)

Note that the complete formula consists of 4 different parts connected by an &-operator:

  • Text string: “Sales in “
  • TEXT function formatting a date: TEXT(C2,”MMMM YYY”)
  • Text string: “: “
  • TEXT function formatting a sales number: TEXT(C3,”$#,##0”)

Each TEXT function consists of the reference to the cell we want to format (C2 and C3) and the format we want to apply (MMMM YYY and $#,##0).

If you want to know more…

How to convert number to text in Excel

As shown in the example mentioned above, Excel TEXT formula takes two arguments:

  • The number to be converted. It can be either a reference to a cell or the actual number typed directly in the formula
  • The format to be applied. It is a pattern described by several special symbols between quotation marks (e.g., ”$#,##0”). The pattern describes how the resulting text should look like. You can find the description of those symbols in the table below:
Symbol Explanation Example
Formula Result
# Placeholder for one digit in a pattern =TEXT(1234.5,”#”) 1234
. (a period) The position of the decimal point in a pattern =TEXT(1234.5,”#.#”) 1234.5
0 and ? Same to # in most cases, except for when used to the right side of the decimal point:  
0 forces the specified number of digits to be displayed after a decimal point. If the number doesn’t have enough decimal digits, zeros are added at the end =TEXT(1234.5,”#.00”)

 

1234.50
drops unnecessary zeros =TEXT(1234.5,”#.##”) 1234.5
– ? drops unnecessary zeros, but displays a space instead of them, so that all decimal points in a column are aligned =TEXT(1234.5,”#.??”) 1234.5  (space after 5)
, (a comma) – If followed by 3 digit placeholders – displays a comma as a thousand separator =TEXT(1234.5,”#,###”) 1,234
– At the end of a pattern – scales the number down by 1000 =TEXT(1234.5,”#.#,”) 1.2
% Displays a number as a percent =TEXT(.12345,”#%,”) 12%

How to convert date to text in Excel

Same as with numbers, to convert a date to text in Excel we specify two arguments in the TEXT formula:

  • The date to be converted. Unlike with numbers, with dates this argument is almost always a reference to a cell. You can type a date directly, but it should be a serial number that Excel uses for storing that date – e.g. 12/31/2015 would be a 42369 – which is not very useful in practice
  • The format to be applied. Same as with numbers, this is a pattern described by special symbols between quotation marks (e.g., ”MMMM YYYY”). Here are some basic guidelines for formatting dates with TEXT function:
    • Month: use a sequence of “m” letters. Depending on the number of “m” letters the month will be displayed differently:
      • “m”: 1, 2, …, 12
      • “mm”: 01, 02, …, 12
      • “mmm”: Jan, Feb, …, Dec
      • “mmmm”: January, February, …, December
      • “mmmmm”: J, F, …, D
    • Day: use a sequence of “d” letters:
      • “d”: 1, 2, …, 31
      • “dd”: 01, 02, …, 31
      • “ddd”: Sun, Mon, …, Sat
      • “dddd”: Sunday, Monday, …, Saturday
    • Year: use a sequence of “y” letters:
      • “yy”: 01, 02, …, 99
      • “yyyy”: 2015, 2016, etc.

A few examples based on the worksheet we used in the previous sections:

Formula Result
=TEXT(C2,”mm/dd/yy”) 12/31/15
=TEXT(C2,”mmm.dd yyyy”) Dec.31 2015
=TEXT(C2,”mm-dd”) 12-31
=TEXT(C2,”dd.mm.yyyy”) 31.12.2015
Test yourself...

String Functions Quiz

Take a quick quiz to understand how well you know Excel string functions:

Share This