What it does
Cell formatting only changes how a value looks; once you put the value inside a text, the formatting is gone. TEXT writes the format into the text itself: ="Done: "&TEXT(B2,"0%") returns "Done: 88%". The result is text, meant for display, not for further calculation.
The format code is the same kind of code you type in Format Cells > Custom, and it depends on Excel's language and regional settings. On an English system, dates use d, m and y ("dd/mm/yyyy", "mmmm") and numbers use a comma for thousands and a dot for decimals ("#,##0.00"). Other language versions use other letters and separators: Turkish Excel writes "gg.aa.yyyy" and "#.##0,00", German Excel "TT.MM.JJJJ". A TEXT formula written on one system can therefore give odd results on a computer set to another language.
Codes that contain only zeros, such as "00000" for padding numbers, or a plain percentage such as "0%", avoid separators and date letters, so they travel well between systems. If you only want the cell to look different, use cell formatting instead: the value stays a number and keeps working in calculations. To turn text back into a number, use VALUE.
Syntax
=TEXT(value, format_text)=METNEÇEVİR(değer; biçim)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
value | Required | The number or date to convert, or a cell that holds it. Text is usually returned unchanged. |
format_text | Required | A format code in quotes: "00000", "0%", "#,##0.00", "dd/mm/yyyy", "mmmm". Codes follow the language of your Excel; to include literal text in the code, wrap it in doubled quotes. |
Examples
1Write a completion rate with a percent sign
The rates in column B are values such as 0.875. The code "0%" multiplies the value by 100, rounds it to a whole number and adds the percent sign.
="Done: "&TEXT(B2,"0%")| A | B | C | |
|---|---|---|---|
| 1 | Project | Completion | Note |
| 2 | Warehouse refit | 87.5% | Done: 88% |
| 3 | Software migration | 40.0% | Done: 40% |
| 4 | Store opening | 100.0% | Done: 100% |
| 5 | Training programme | 62.0% | Done: 62% |
- C2 (filled down to C5)
="Done: "&TEXT(B2,"0%")
C2 shows Done: 88%; without TEXT, the rate would be joined as 0.875. C4 shows Done: 100%.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Pad order numbers to five digits
Order numbers are stored as numbers such as 42 and 1180. The code "00000" pads each one to five digits, and the prefix "SO-" is added in front.
="SO-"&TEXT(A2,"00000")| A | B | C | |
|---|---|---|---|
| 1 | Order | Customer | Order Code |
| 2 | 42 | Brown & Sons | SO-00042 |
| 3 | 1180 | Northgate Ltd | SO-01180 |
| 4 | 7 | Harbour Trade | SO-00007 |
| 5 | 31025 | Star Market | SO-31025 |
- C2 (filled down to C5)
="SO-"&TEXT(A2,"00000")
C2 shows SO-00042 and C4 shows SO-00007. 31025 already has five digits, so it is unchanged: SO-31025.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Build a year-month key from a date
Month and day names depend on Excel's language, so this example uses only numbers. Column B takes the year as text; column C adds the month number padded to two digits, giving a key that sorts correctly.
=TEXT(A2,"yyyy")| A | B | C | |
|---|---|---|---|
| 1 | Delivery Date | Year | Period |
| 2 | 15-Mar-2025 | 2025 | 2025-03 |
| 3 | 1-Jul-2025 | 2025 | 2025-07 |
| 4 | 29-Oct-2025 | 2025 | 2025-10 |
| 5 | 31-Dec-2025 | 2025 | 2025-12 |
- B2 (filled down to B5)
=TEXT(A2,"yyyy") - C2 (filled down to C5)
=TEXT(A2,"yyyy")&"-"&TEXT(MONTH(A2),"00")
The first row shows 2025 and 2025-03; the last shows 2025-12. On an English system, "mmmm" and "dddd" would give the month and weekday names.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Odd letters in the result
Cause: The format code was written for another language: Excel set to Turkish or German does not read
"dd/mm/yyyy"as day, month and year.Fix: Write codes for the language of the Excel that will open the file. If the file travels between languages, build dates from DAY, MONTH and YEAR instead.
- Result is not counted
Cause: TEXT returns text, so SUM skips it and comparisons do not behave as expected.
Fix: Use cell formatting for values you calculate with and keep TEXT for display.
Good to know
- On an English system,
=TEXT(A2,"mmmm")returns the month name and=TEXT(A2,"dddd")the weekday; other language versions use different letters for these codes. - To build a sortable period key such as 2025-03 that works on any system, combine numbers instead of date codes:
=YEAR(A2)&"-"&TEXT(MONTH(A2),"00").
In Nexell
Get Nexell to do it from a sentence
Add a Note column that writes each project's completion rate as "Done: 88%"
Convert to tableBring your project list into the tool and type this. Nexell builds the Note column with real Excel formulas and shows it in the preview; in the downloaded file, the note follows any change in the rate.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I get the month name from a date in Excel?
On an English system, =TEXT(A2,"mmmm") returns the full month name (March) and "mmm" the short one (Mar). Month names follow the language of Excel, and the code letters differ in other language versions. If you need the month as a number, use MONTH.
What is the difference between TEXT and cell formatting?
Cell formatting changes only the look; the value stays a number and works in calculations. TEXT turns the value into text; use it to carry a formatted look into a sentence or a joined text, not for calculations.
How do I add leading zeros to a number in Excel?
=TEXT(A2,"00000") pads the number to five digits: 42 becomes "00042". If the value should stay a number, give the cell the custom format 00000 instead.