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")
Example 2
=TEXT(2141,"00000")&" is my ZIP code."
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".