TEXT function

Description

If you want to combine text and a number, like a date, into a readable format use the TEXT function. For example, if you have the following information in separate cells, you can use TEXT to create a meaningful sentence.

Example of data in individual cells that will be concatenated with the TEXT function
Sample data to combine


Example of the TEXT function in use.  Formula 1 is ="Date: "&TEXT(A2,"mm/dd/yyyy").  Formula 2 is ="Date-time: "&TEXT(A2,"m/d/yyyy h:mm AM/PM")
Result with the TEXT function

Syntax

TEXT(value, format_text)

The TEXT function syntax has the following arguments:

Argument Name

Description

value

Required. A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.

format_text

Required. A numeric format as a text string enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00". The format_text argument is not case sensitive.

In its simplest form, the TEXT function says:

  • TEXT(Value you want to format, “Format you want to apply to that value”)

  • =TEXT(TODAY(),"MM/DD/YY") would return today’s date in MM/DD/YY format, like 03/14/12

  • ="Today's date is "&TEXT(TODAY(),"mm/dd/yy") would return “Today’s date is 03/14/12”

Note: Combining data is called “concatenation”. Conversely, if you want to split data apart, or extract certain data from a cell it’s called “parsing”. See these links for Splitting or extracting data from cells.

Frequently Asked Questions

Common scenario

Let’s say you want to combine text and a number value, like “Report Printed on: 03/14/12”, or “Weekly Revenue: $66,348.72”. You could type that into Excel manually, but that defeats the purpose of having Excel do it for you. In the following example, you’ll see what happens if you try to do this without using the TEXT function. In this case, we’re using the Ampersand operator (&) to concatenate a text string, a space and a value with =A2&” “&B2.

Examples of concatenating cell values without using TEXT.  Formula is =A2&" "&B2

What happened? Unfortunately, Excel doesn’t know how you want to display your numbers, so it converts them to their basest level, a serial number for the date and time, and general format for the currency value. This is where the TEXT function is invaluable, because it allows you to force Excel to format the values however you want them.

Examples of contatenating cell values with TEXT.  Formula is =A2&TEXT(B2,""mm/dd/yy") where A2 = "Report printed on: " and B2 = 03/14/12.

Now our formulas are:

  • Cell C2:=A2&" "&TEXT(B2,"mm/dd/yy") - Date format

  • Cell C3: =A3&" "&TEXT(B3,"HH:MM AM/PM") - Time format

  • Cell C4: =A4&" "&TEXT(B4,"$#,###.##") – Currency format

How do I find my format string?

If you’re wondering how you would find the format string you’re after, don’t worry, we’re going to show you how to get Excel to give it to you. If you haven't already formatted your reference cell, let’s say B2 in the previous example, select it, right-click on it, then choose Format > Cells > Number, or use CTRL+1 ( Image of the MAC Command button icon +1 or Control + 1 on the Mac), and select the format you want. In this case, Excel will show that B2 is formatted as Date > 03/04/12, or “mm/dd/yy” format. Next, select the Custom option, and you’ll see Excel has put the format you selected in the Type box. In this case you don't need to worry about the ";@" string, it's just a placeholder and Excel won't care if it's there or not.

Example of using the Format > Cells > Number > Custom dialog to have Excel create format strings for you.

Now, all you need to do is copy the format string from the Format > Cells dialog and paste it into your TEXT formula.

=A2&" "&TEXT(B2,"mm/dd/yy")

Note: You can certainly use the TEXT function to change the value of a single cell or range of cells, but there’s no practical use for that when you can simply use the Format > Cells dialog to have Excel apply the desired format for you.

Following are some examples of how you can apply different number formats to your values. We’re not concatenating the values with anything, these are just examples so you can see various format strings in action.

Examples of General, Decimal and Fraction number format strings.

“???.???” > Values with aligned decimals

"# ???/???” > Values with aligned fractions

Excel separates thousands by commas if the format contains a comma (,) that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format_text argument is "#,###.0,", Excel displays the number 12,200,000 as 12,200.0.

Examples of Decimal and Thousands (comma) separators.

Display days, months, and years - To display numbers as date formats (such as days, months, and years), use the following codes in the format_text argument, like “mm/dd/yy” to display “03/14/12”. You can use any separator between them that you want, like the forward slash (/), hyphen (-), period (.), etc. The format strings can be lower or upper case, that’s simply a matter of preference.

Examples of Months, Day and Year format strings.

Display hours, minutes and seconds - To display numbers in time formats (such as hours, minutes, and seconds), use the following codes in the format_text argument, like “hh:mm:ss AM/PM” to display “12:15:30 PM”. Unlike with dates, you need to use a colon (:) as a separator until you get to hundredths of a seconds, which requires a period (.).

Examples of formatting Hours, Minutes and Seconds with the TEXT function

AM/PM, am/pm, A/P, a/p - Displays the hour based on a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight.

Combine Dates & Times – You can easily combine date and time format strings as well, like =TEXT(A2, "m/d/yyyy h:mm AM/PM").

Include currency symbols - To precede a number with a dollar sign ($), type the dollar sign at the beginning of the format_text argument (for example, "$#,##0.00"). To enter one of the following currency symbols in a number format, hold the Alt key and use the numeric (10-Key) keypad to type the ANSI code for the symbol.

Input Symbols with ALT+ Numeric Keypad inputs.  Example: ALT+0162 for US Cents.

Note: You must use the numeric (10-Key) keypad; using the Alt key with the number keys in the top row of the keyboard will not generate ANSI codes.

Display percentages - To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% - include the percent sign (%) in the format_text argument.

Display scientific notations - To display numbers in scientific (exponential) format, use the following exponent codes in the format_text argument.

E (E-, E+, e-, e+) - Displays a number in scientific (exponential) format. Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved. For example, if the format_text argument is "0.00E+00", Excel displays the number 12,200,000 as 1.22E+07. If you change the format_text argument to "#0.0E+0", Excel displays 12.2E+6.

If you include any of the following characters in the format_text argument, they are displayed exactly as entered.

Examples of characters that will be interpreted literall and don't need format strings, like +.

The format_text argument cannot contain an asterisk (*).

Using the TEXT function converts a numeric value to formatted text, and the result can no longer be calculated as a number. To format a cell so that its value remains numeric, right-click the cell, click Format Cells or Ctrl+1, and then in the Format Cells dialog box, on the Number tab, set the formatting options you want. For more information about using the Format Cells dialog box, click the Help button Button Image in the upper right corner of the dialog box.

Format Currency & Percentage

Example of data in individual cells that will be concatenated with the TEXT function


Example of the TEXT function in use.  Formula 1 is ="Date: "&TEXT(A2,"mm/dd/yyyy").  Formula 2 is ="Date-time: "&TEXT(A2,"m/d/yyyy h:mm AM/PM")

Date & Time

Examples of using TEXT to format Dates.  =TEXT(A2,"mm/dd/yyyy")

Scientific & Currency

Examples of using TEXT to format Scientific Notation and Currency.


Frequently Asked Questions
  1. How can I retain or add leading 0’s?

    • To retain leading zeros format the input range as Text prior to pasting or entering values.

    • If you’ve entered data and Excel has truncated your leading zeros, you can use the TEXT function to add them back. You can reference the top cell with the values and use =TEXT(value,”00000”), where the number of 0’s in the formula represents the total number of characters you want, then copy and paste to the rest of your range.

      Examples of using TEXT to format leading zeros.  =TEXT(A2,"00000")
    • If you’ve used the TEXT function to add leading zeros or force a number to take a specific format, but you need to convert your values back to numbers you can use what’s called the double-unary operator (--): =--CellReference, like cell D2 in this example, which uses =--C2 to convert “00001” to an actual 1.

  2. Can I add a new line (line break) with the TEXT function, like I can with Alt+Enter in a cell?

    • Yes, but it takes a few steps. First, select the cell or cells where you want this to happen and go to Format > Cells > Alignment > Text control > check the Wrap Text option. Next, adjust your completed TEXT function to include the ASCII function CHAR(10) where you want the line break. You might need to adjust your column width depending on how the final result aligns.

      Example of using TEXT with CHAR(10) to insert a line break. ="Today is: "&CHAR(10))&TEXT(TODAY(),"MM/DD/YY")
    • In this case, we used: ="Today is: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy")

  3. How can I convert numbers to text, like 123 to One Hundred Twenty-Three?

  4. Can I color format just my number values in the TEXT function result?

    • No. While the Format > Cells dialog will let you color format individual cells based on a condition (like a negative value), the only way to do this within the TEXT function is to use Visual Basic for Applications (VBA) code.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

See Also

Create or delete a custom number format

Split text into different cells with Data > Text to Columns

Split text into different cells with functions

CONCATENATE function

CONCAT function (Office 365, Excel 2016 and later)

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

Keyboard shortcuts in Excel 2016 for Windows

Keyboard shortcuts in Excel 2016 for Mac

Excel functions (alphabetical)

Excel functions (by category)

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×