Excel TEXT Function: Dates and Number Formats
By Szabó Gergő · Updated
TEXT converts a number or date into text using a format code. It is the reliable way to keep readable dates and padded IDs when you combine values with & or TEXTJOIN.
Syntax and arguments
=TEXT(value, format_text)- value
- The number, date, or cell to format.
- format_text
- A format code in quotes, such as "yyyy-mm-dd" or "00000".
TEXT examples
Invoice date is B2.
="Due "&TEXT(B2,"mmm d, yyyy")Without TEXT, Excel would concatenate the date serial number.
Numeric ID is A2.
=TEXT(A2,"00000")7 becomes 00007 as text, which is useful for lookups that expect codes.
Amount is C2.
=TEXT(C2,"$#,##0.00")The result is text that displays a dollar amount with thousands separators.
Common mistakes
Using TEXT when you still need a number
Keep the original numeric cell for math; use TEXT only for labels.
Locale format codes
US-English Excel uses m for month and d for day. Do not mix in locale-specific tokens unless you know the file language.
Rounding hidden by a format
TEXT("0") drops decimals. Choose a format that matches the precision you need.
TEXT FAQ
Does TEXT change the stored value?
The original cell stays numeric. The TEXT result is a new text value.
How do I keep leading zeros?
Use a format such as "00000" or store the source as text.
Can I format percentages?
Yes. =TEXT(A2,"0.0%") multiplies the display by 100 and adds a percent sign.