---
title: "TEXT function in Excel: syntax and examples | ExcelSavvy"
description: "How to use TEXT in Excel and Google Sheets: format numbers and dates as text with format codes."
canonical_url: "https://excelsavvy.com/functions/text"
meta:
  "og:description": "How to use TEXT in Excel and Google Sheets: format numbers and dates as text with format codes."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~VEVYVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBURVhUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBmb3JtYXQgbnVtYmVycyBhbmQgZGF0ZXMgYXMgdGV4dCB3aXRoIGZvcm1hdCBjb2Rlcy4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVCh2YWx1ZSwgZm9ybWF0X3RleHQp%2Cp_Ii9mdW5jdGlvbnMvdGV4dCI%2Cs_AJqCXsLKdg_rQ4ig.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "TEXT function in Excel: syntax and examples | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~VEVYVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBURVhUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBmb3JtYXQgbnVtYmVycyBhbmQgZGF0ZXMgYXMgdGV4dCB3aXRoIGZvcm1hdCBjb2Rlcy4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVCh2YWx1ZSwgZm9ybWF0X3RleHQp%2Cp_Ii9mdW5jdGlvbnMvdGV4dCI%2Cs_AJqCXsLKdg_rQ4ig.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **The TEXT function**

Formats a number as text with a number format code, for example to put it inside a sentence.

=

TEXT(

## **How to use TEXT**

TEXT turns a number or date into text that looks exactly the way you describe. Think of a stencil: the number is the paint, and the format code is the cutout that decides what shows through.

Reach for it when a number has to sit inside a sentence. `**="Growth: "&A2**` pastes in the raw number, while `**="Growth: "&TEXT(A2,"0%")**` shows 25% when A2 holds 0.25. It also keeps the leading zeros in codes like 00042, prints a date as Mar 15, 2024 with `**=TEXT(A2,"mmm d, yyyy")**`, and pulls out just the month name with `**"mmmm"**` or the weekday with `**"dddd"**`.

The first argument is the value, usually a cell. The second is the format code in quotes. A `**0**` is a digit that always shows, a `**#**` is a digit that only shows when needed, `**.**` is the decimal point, `**,**` the thousands separator and `**%**` multiplies by 100. So `**#,##0.00**` turns 1234.5 into 1,234.50.

Remember that the result is text. Keep the real number in its own cell and use TEXT only where the result is displayed, because SUM skips text. To get a number back from text, use VALUE. When you are joining many pieces into one string, pair TEXT with the & operator or with TEXTJOIN.

## **Syntax**

```
TEXT(value, format_text)
```

<dl><dt>**value **</dt>
<dd>The number or date to format.</dd>
<dt>**format_text **</dt>
<dd>A format code in quotes, such as "0%", "00000" or "dd-mm-yyyy".</dd></dl>

## **Examples**

B1`=TEXT(<sup>1</sup>0.25,<sup>2</sup>"0%")`

|  | A | B |
| --- | --- | --- |
| 1 |  | **25%** |

**In plain words:** Show <sup>1</sup>0.25 <sup>2</sup>as a percentage with no decimals.

B1=TEXT(42,"00000")

|  | A | B |
| --- | --- | --- |
| 1 |  | **00042** |

B1=TEXT(1234.5,"#,##0.00")

|  | A | B |
| --- | --- | --- |
| 1 |  | **1,234.50** |

B1=TEXT(DATE(2024,3,5),"dd-mm-yyyy")

|  | A | B |
| --- | --- | --- |
| 1 |  | **05-03-2024** |

## **Common TEXT mistakes**

- SUM returns 0 on my TEXT results

  TEXT turns the number into text, and SUM skips text. Keep the real number in its own cell and use TEXT only for the display, or convert the text back with `**=VALUE(B2)**`.
- A date comes out as a five-digit number inside my sentence

  Joining a date to text with & uses the raw date number. Wrap the date in TEXT: `**="Due: "&TEXT(A2,"mmm d, yyyy")**`.
- My TEXT formula shows the wrong letters on a colleague's computer

  Format codes follow the language of Excel. English uses `**yyyy**` for the year, Danish uses `**åååå**` and German `**JJJJ**`. Numbers differ too: the decimal point and thousands separator swap places in many languages. Write the code the way the Excel that opens the file expects it.
- TEXT returns #NAME?

  The format code is missing its quotes. It must be text, so write `**=TEXT(A2,"0.00")**` with the quotes.

## **Read the guide**

- [**Dates in Excel: how they work and the formulas you need**](https://excelsavvy.com/learn/excel-dates)
- [**How to merge cells in Excel, and what to use instead**](https://excelsavvy.com/learn/merge-cells-excel)
- [**How to make a graph in Excel: column, line and pie charts step by step**](https://excelsavvy.com/learn/excel-charts)
- [**Excel calendar 2026: build your own with week numbers and holidays**](https://excelsavvy.com/learn/excel-calendar-2026)

## **Questions**

<details>

<summary>

### Why does TEXT give a different result in Danish Excel?

</summary>

Format codes follow the language of Excel. Danish Excel swaps the thousands and decimal separators and uses å for year, so "dd-mm-yyyy" is written "dd-mm-åååå".

</details>

<details>

<summary>

### How do I format a date with TEXT?

</summary>

Put a date code in the second argument, such as =TEXT(A2,"mmm d, yyyy") for Mar 15, 2024 or =TEXT(A2,"mmmm") for the month name. Use "dddd" for the weekday.

</details>

<details>

<summary>

### How do I add leading zeros with TEXT?

</summary>

Use one zero per digit you want in total. =TEXT(42,"00000") returns 00042.

</details>

<details>

<summary>

### How do I show a number as currency with TEXT?

</summary>

Add the symbol to the code: =TEXT(1234.5,"$#,##0.00") returns $1,234.50. Use the same code without the $ sign for a plain number with thousands separators.

</details>

<details>

<summary>

### Does TEXT work the same in Google Sheets?

</summary>

The common codes such as 0%, 00000 and #,##0.00 work the same. Google Sheets follows the locale of the spreadsheet for decimal and date codes, so check that setting if a result looks off.

</details>

## **Related functions**

- [**CONCAT**](https://excelsavvy.com/functions/concat)
- [**TEXTJOIN**](https://excelsavvy.com/functions/textjoin)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/functions/text#webpage","dateModified":"2026-11-16","description":"How to use TEXT in Excel and Google Sheets: format numbers and dates as text with format codes.","name":"TEXT function in Excel: syntax and examples","url":"https://excelsavvy.com/functions/text","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/functions/text#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/functions/text#/schema/question/1"},{"@id":"https://excelsavvy.com/functions/text#/schema/question/2"},{"@id":"https://excelsavvy.com/functions/text#/schema/question/3"},{"@id":"https://excelsavvy.com/functions/text#/schema/question/4"},{"@id":"https://excelsavvy.com/functions/text#/schema/question/5"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/functions/text"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/functions/text#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://excelsavvy.com/functions","position":2},{"@type":"ListItem","name":"TEXT function in Excel: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/functions/text#article","dateModified":"2026-11-16","description":"How to use TEXT in Excel and Google Sheets: format numbers and dates as text with format codes.","headline":"TEXT function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~VEVYVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBURVhUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBmb3JtYXQgbnVtYmVycyBhbmQgZGF0ZXMgYXMgdGV4dCB3aXRoIGZvcm1hdCBjb2Rlcy4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVCh2YWx1ZSwgZm9ybWF0X3RleHQp%2Cp_Ii9mdW5jdGlvbnMvdGV4dCI%2Cs_AJqCXsLKdg_rQ4ig.png","@type":["Article","TechArticle"],"author":{"@id":"https://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/functions/text#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/functions/text#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.com/functions/text#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why does TEXT give a different result in Danish Excel?","acceptedAnswer":{"@type":"Answer","text":"Format codes follow the language of Excel. Danish Excel swaps the thousands and decimal separators and uses å for year, so \"dd-mm-yyyy\" is written \"dd-mm-åååå\"."}},{"@id":"https://excelsavvy.com/functions/text#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I format a date with TEXT?","acceptedAnswer":{"@type":"Answer","text":"Put a date code in the second argument, such as =TEXT(A2,\"mmm d, yyyy\") for Mar 15, 2024 or =TEXT(A2,\"mmmm\") for the month name. Use \"dddd\" for the weekday."}},{"@id":"https://excelsavvy.com/functions/text#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I add leading zeros with TEXT?","acceptedAnswer":{"@type":"Answer","text":"Use one zero per digit you want in total. =TEXT(42,\"00000\") returns 00042."}},{"@id":"https://excelsavvy.com/functions/text#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"How do I show a number as currency with TEXT?","acceptedAnswer":{"@type":"Answer","text":"Add the symbol to the code: =TEXT(1234.5,\"$#,##0.00\") returns $1,234.50. Use the same code without the $ sign for a plain number with thousands separators."}},{"@id":"https://excelsavvy.com/functions/text#/schema/question/5","@type":"Question","inLanguage":"en-US","name":"Does TEXT work the same in Google Sheets?","acceptedAnswer":{"@type":"Answer","text":"The common codes such as 0%, 00000 and #,##0.00 work the same. Google Sheets follows the locale of the spreadsheet for decimal and date codes, so check that setting if a result looks off."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~VEVYVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBURVhUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBmb3JtYXQgbnVtYmVycyBhbmQgZGF0ZXMgYXMgdGV4dCB3aXRoIGZvcm1hdCBjb2Rlcy4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVCh2YWx1ZSwgZm9ybWF0X3RleHQp%2Cp_Ii9mdW5jdGlvbnMvdGV4dCI%2Cs_AJqCXsLKdg_rQ4ig.png","inLanguage":"en-US","url":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~VEVYVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBURVhUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBmb3JtYXQgbnVtYmVycyBhbmQgZGF0ZXMgYXMgdGV4dCB3aXRoIGZvcm1hdCBjb2Rlcy4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVCh2YWx1ZSwgZm9ybWF0X3RleHQp%2Cp_Ii9mdW5jdGlvbnMvdGV4dCI%2Cs_AJqCXsLKdg_rQ4ig.png"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
