Function: TEXT

by | Jan 16, 2019 | Excel Formulas

TEXT Function

Excel TEXT function helps apply a certain formatting to a numeric value. You can think this function as an alternative to number formatting. In this guide, we’re going to show you how to use the TEXT function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Excel TEXT Function Syntax

TEXT(number,format)

Arguments

Number The number you want to format.
Format A text string that defines the formatting.


Examples

Example 1

="International beer day is at "&TEXT(43679,"dddd, mmm dd, yyyy")
formula returns International beer day is at Friday, Aug 02, 2019. The TEXT function converts the date-time value, 43679, to a string version that contains day name, short month name, or day number and year.

Example 2

=TEXT(2141,"00000")&" is my ZIP code."
formula returns 2141 as 02141 which is the official use of ZIP codes in US. If you try to enter a number with leading zeroes, Excel will ignore and remove them. Excel TEXT function can be used for 3 or 4-digit ZIP codes with leading zeroes.

Download Workbook

Tips

  • Number Formatting in Excel – All You Need to Know article contains detailed information about formatting options.
  • Number Format dialogue can be used as a cheat sheet. When a cell is selected, press Ctrl + 1 to open Number Format and select the formatting you need. Go to Custom category to see code version of the recently selected format.
  • Although the TEXT function converts numerical values to text and removes the possibility of using them in calculations, it can be useful in certain situations like when you need to format only a sub-string in a sentence.

Issues

#NAME!

Format is a string, so it must be entered using quotation marks like "m/d/yyyy". Otherwise, the TEXT function returns a #NAME! error.

Language differences

The TEXT function is language-specific, and requires using region-specific format codes for date and time. For example, in English; "d", "m" and "y" placeholders are used for day, month and year respectively. On the other hand, a different language, for example Turkish, uses "g", "a" and "y".