Knowledge base / Excel
Excel TEXT: number formats, dates and text results
Format numbers, percentages, dates and times as text while keeping numerical source values available for further calculations.

TEXT converts a numerical value into a string using a specified format code. Its Polish name is TEKST. I use it when building a message containing a date or amount that needs a particular appearance. I keep the original numerical input separately for calculations.
If the goal is simply to display two decimal places, consider ordinary cell formatting. That preserves the numerical value. TEXT produces a string even when the visible result consists entirely of digits.
Syntax and example settings
=TEXT(value,format_text)
The first argument is a numerical value or reference. The second is a quoted format code. The Microsoft reference covers Microsoft 365 and Excel releases including 2016, 2019, 2021 and 2024.
The synthetic examples assume US English settings: a decimal point, comma thousands separator and argument commas. Other environments may require different notation. Translating the function name does not automatically adapt a quoted format code.
Decimal places, percentages and leading zeros
| Formula | Text result | Purpose |
|---|---|---|
=TEXT(12.3,"0.00") |
12.30 |
Two decimal places |
=TEXT(1234.5,"#,##0.00") |
1,234.50 |
Thousands grouping and two places |
=TEXT(0.25,"0%") |
25% |
Percentage representation of a fraction |
=TEXT(42,"00000") |
00042 |
At least five digits |
A zero in the code requires a digit at that position. A # allows an unnecessary digit to be omitted. For 42, the five zero pattern adds three leading zeros. It does not recover missing source knowledge: the intended identifier length must come from your data rules.
For identifiers that originally contain leading zeros, preserve them as text during import. Adding zeros afterwards is justified only when you know the required format. It cannot restore digits already lost through numerical storage of a long identifier.
Dates and times depend on local codes
Suppose A2 holds a genuine date of 10 June 2024, and A3 holds a genuine time of 10:45:30. These are actual spreadsheet values rather than ambiguous strings waiting to be interpreted.
=TEXT(A2,"dd.mm.yyyy")
=TEXT(A3,"hh:mm")
=TEXT(A3,"hh:mm:ss")
Expected strings are 10.06.2024, 10:45 and 10:45:30. In Polish notation, corresponding examples use TEKST, year code rrrr and hour code gg. The earlier Polish article incorrectly copied English year and hour codes into local examples.
The meaning of m depends on context. In a date pattern it represents a month; immediately after hours or before seconds it represents minutes. A complete time pattern such as hh:mm therefore matters. A standalone mm is not a general instruction to extract minutes.
The custom format guide describes the components. For cross language work, see the formula translation guide. Do not assume a quoted pattern automatically rewrites itself for every language environment.

Obtain a code from the local format dialog
In desktop Excel, select a representative value, open Format Cells and choose the intended appearance. Then inspect the custom format category and its type field. Use the appropriate code as the quoted argument to TEXT.
Microsoft describes this approach on the function page. It helps with regional separators and date notation. Complex codes can contain separate sections for different values, so avoid copying a fragment without understanding its role. Begin with a simple rule that matches one task.
To append a literal unit, you can concatenate a separate string. With 12.3 in A4, =TEXT(A4,"0.00")&" kg" returns 12.30 kg. This avoids an extra layer of quotation marks inside the format code. An apostrophe is not the general delimiter for literal text within that code.
Building a complete message
For the date in A2, ="Report date: "&TEXT(A2,"yyyy-mm-dd") returns Report date: 2024-06-10. Concatenating a label and a date without an explicit format may expose the numerical representation rather than the desired date appearance.
The actual date remains in A2. I use it for chronological sorting, period filters and date differences. The message column serves presentation. The next person should not need to extract a date from a sentence to continue the analysis.
I also avoid the categorical claim that no mathematical operation can ever use a text result. Excel can sometimes coerce numerical text, depending on its representation. The more dependable design is to calculate from the original numerical input, as Microsoft recommends.
Limits and common mistakes
Percentage formatting does not correct a mistaken scale. The fraction 0.25 produces 25%; the number 25 with the same pattern produces 2500%. Establish the source convention before adding percentage notation. A different display code cannot fix an incorrect denominator.
A format also does not convert currencies or units. Appending kg does not turn grams into kilograms, and a currency label does not perform conversion. Prepare the correct value first, then present it. A plausible label can otherwise conceal a measurement error.
Microsoft also notes that colour sections in a format code do not colour the TEXT result. Use cell or conditional formatting for that purpose. A returned string does not inherit every visual property of the original cell.
Exercise: separate the value and its label
Enter the number 42 into A2. In B2 use =TEXT(A2,"00000"); in C2 use =A2+1. Expect the string 00042 in B2 and the number 43 in C2. The calculation refers to the source rather than the presentation label.
Now change A2 to 7. The label becomes 00007 and the calculated result becomes 8. Finally, state why five digits are appropriate for this identifier. The function can apply a pattern, but cannot establish whether that pattern matches the source data’s rules.
Key takeaways
Does TEXT return a number?
It returns a string. Use the original numerical input in a separate cell for subsequent calculations.
Are date format codes identical in Polish and English Excel?
They can differ: Polish uses rrrr for a four digit year where English uses yyyy. Obtain the code from the local Format Cells dialog.
How do I add leading zeros?
For a known five digit format, TEXT(42,"00000") returns 00042. It applies an agreed rule rather than recovering missing information.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
