Skip to content

Excel function · Text

Excel TEXT function

TEXT turns a number or date into text using the format code you give it: 42 becomes "00042", 0.875 becomes "88%". It is how you put a formatted value inside a sentence or a code.

In Turkish Excel: METNEÇEVİR

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

English Excel=TEXT(value, format_text)
Turkish Excel=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

ArgumentRequiredDescription
valueRequiredThe number or date to convert, or a cell that holds it. Text is usually returned unchanged.
format_textRequiredA 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.

C2fx="Done: "&TEXT(B2,"0%")
Write a completion rate with a percent sign: example table
ABC
1ProjectCompletionNote
2Warehouse refit87.5%Done: 88%
3Software migration40.0%Done: 40%
4Store opening100.0%Done: 100%
5Training programme62.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.

C2fx="SO-"&TEXT(A2,"00000")
Pad order numbers to five digits: example table
ABC
1OrderCustomerOrder Code
242Brown & SonsSO-00042
31180Northgate LtdSO-01180
47Harbour TradeSO-00007
531025Star MarketSO-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.

B2fx=TEXT(A2,"yyyy")
Build a year-month key from a date: example table
ABC
1Delivery DateYearPeriod
215-Mar-202520252025-03
31-Jul-202520252025-07
429-Oct-202520252025-10
531-Dec-202520252025-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

Type this in Nexell

Add a Note column that writes each project's completion rate as "Done: 88%"

Convert to table

Bring 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.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free