TEXT Function in Google Sheets: Format Numbers and Dates

The Google Sheets TEXT function converts a number, date, or time into text displayed in a format you specify. Its syntax is =TEXT(value, format_pattern); for example, if A2 contains 1234.5, =TEXT(A2,"$#,##0.00") returns the text $1,234.50. Use it when you need a controlled display inside a formula, such as a sentence, label, or report heading.

What the TEXT function does

Sheets stores numbers and dates as values that can be calculated, sorted, and formatted. TEXT takes such a value and returns a text string that follows a pattern you provide. The result may look like a number or date on the sheet, but it is text, not the original numeric value. That distinction is the key to using the function correctly.

The function has two arguments: the value to convert and a format pattern written inside quotation marks. The value can be a cell reference or a calculation. The pattern tells Sheets which digits or date parts to show, whether to include separators, and how to display units such as percentages or currency. In Google Sheets in a browser, enter the formula in a cell and press Enter; the examples here use comma-separated arguments.

TEXT is useful when a value must be joined to words while retaining a chosen display. It is usually unnecessary just to make a cell look like currency or a date: cell formatting changes the appearance without turning the underlying value into text.

Number patterns: decimals, grouping, and percentages

For numbers, patterns are built from placeholders. The zero character forces a digit to appear, while the number sign (#) allows a digit to appear only when needed. A comma can group thousands, and a period marks the decimal place in the pattern. The final display can reflect your spreadsheet’s locale, so punctuation may look different in sheets configured for another region.

0 displays at least one digit.
0.00 displays two decimal places, adding trailing zeroes where needed.
#,##0 groups thousands and displays no decimal places.
0% displays a proportion as a percentage with no decimal places.
0.0% displays a percentage with one decimal place.

For example, with 1234.5 in A2, the pattern #,##0.00 displays 1,234.50. With 0.256 in A2, 0.0% displays 25.6%. Percentage patterns scale a proportion for display: a stored value of 0.256 becomes 25.6%, while a stored value of 25.6 becomes 2,560%. If the result seems one hundred times too large, check whether the source is already expressed as a whole-number percentage.

To add a currency symbol, put it in the pattern. With 1234.5 in A2, $#,##0.00 produces $1,234.50 in a typical US locale. Currency symbols and separators can vary with spreadsheet locale, and a hard-coded dollar sign is not the same as choosing a currency format that adapts to regional settings.

Format dates and times

Dates and times in Sheets are stored as numeric values, which is why a date can be formatted in different ways or used in date arithmetic. TEXT converts that value into a text representation. Common date pattern letters include d for day, m for month, and y for year. Repeating a letter changes the level of detail: for example, mmm gives an abbreviated month name, while mmmm gives the full name.

Assume A2 contains a genuine Sheets date for September 7, 2026. The formula =TEXT(A2,"mmmm d, yyyy") returns September 7, 2026 as text. With the same date, =TEXT(A2,"yyyy-mm-dd") returns 2026-09-07, a compact order often used in filenames or data exports. The precise language of month and day names may depend on the spreadsheet’s locale.

Time patterns use h or hh for hours and mm for minutes; seconds can be included with ss. An AM/PM marker can distinguish a 12-hour clock from a 24-hour display. If A2 contains a time value of 14:05, =TEXT(A2,"h:mm AM/PM") returns 2:05 PM. A date-time value can use date and time tokens together, such as mmm d, yyyy h:mm AM/PM.

In date and time patterns, the letter m can mean month or minute depending on its position in the pattern. Near hour or second tokens it is interpreted as minutes; otherwise it generally represents a month. If a time pattern produces unexpected output, check the placement of m and the format used by the source cell.

Use TEXT inside labels and sentences

A common reason to use TEXT is to combine a value with descriptive words. Directly joining a number to a sentence can show too many decimal places or an unhelpful raw value. Formatting the number first makes the output predictable. This is useful for dashboard headings, mail-merge-style messages, summary labels, and chart annotations.

Assume A2 contains the numeric value 2.4. The formula =TEXT(A2,"0.0")&" kg in stock" returns the text “2.4 kg in stock.” The ampersand joins the formatted result to the words. If A2 contains 2.46, the same pattern rounds the displayed number to one decimal place, producing “2.5 kg in stock”; it does not change the value stored in A2.

For a report label, assume B2 contains a date and C2 contains a total. The formula ="Sales for "&TEXT(B2,"mmmm yyyy")&": $"&TEXT(C2,"#,##0.00") can produce a label such as “Sales for September 2026: $1,234.50.” The displayed total is rounded according to the pattern, while the original value remains untouched. This is often more reliable than depending on a cell’s visible formatting when constructing text.

TEXT versus formatting a cell

Choose ordinary cell formatting when your goal is simply to display a numeric value with commas, currency, percentage, or a date style. Select the cells, then use Format > Number and choose a suitable category. For a pattern that is not listed, use Format > Number > Custom number format. Cell formatting preserves the numeric value, so formulas such as SUM can continue to use it as a number.

Choose TEXT when the formatted value must become part of a text result or when a formula needs to return a string in a particular style. For example, a date used in a sentence needs a formatted string to avoid displaying its underlying serial number. The trade-off is that the result is text. A later SUM will not treat a string such as “1,234.50” as an ordinary numeric input in the same way it treats the original number.

A useful rule is to format the cell for presentation and use TEXT for composition. Avoid converting an entire column of calculation inputs into text unless the values are intended to be labels or exported strings.

Common problems and how to fix them

The result looks like a date serial or an unexpected number: Confirm the cell contains a real date or numeric value, then check the pattern. A date typed or imported as plain text may not behave like a date serial.
A percentage is far too large: Check the stored value. A percentage pattern multiplies a proportion by 100 for display, so 0.25 becomes 25%, but 25 becomes 2,500%.
The result has too many or too few decimal places: Use zero placeholders such as 0.00 to force decimal places, or optional # placeholders where trailing zeroes should not be required.
A following calculation does not work as expected: Remember that TEXT returns text. Keep the original numeric cell for arithmetic, or convert a text number to a value with an appropriate conversion method before calculating.
Names or separators differ from an example: Check the spreadsheet locale. It can affect decimal and grouping marks, date conventions, and the language used for month or weekday names. Formula argument separators may also differ in some locale settings.

Another common misconception is that TEXT changes how the source cell is stored. It does not. It creates a formatted text result in the formula cell; the referenced value remains unchanged. If you need the result to sort chronologically or participate in date calculations, keep it as a date value and apply cell formatting instead.

Choosing a pattern and checking the result

When deciding on a pattern, start with what the audience needs to read, not with the longest possible format. A compact date may be easier to scan in a table, while a full month name can be clearer in a sentence. For amounts, decide whether decimals matter and whether the symbol should be explicit. For percentages, decide how many decimal places are meaningful rather than adding precision that the source data does not support.

Test a pattern with edge cases before filling it down a column. Useful checks include a whole number, a value with several decimals, zero, a negative amount, and a blank source cell if blanks occur in the data. TEXT formats a supplied value; it does not define business rules for missing data or validate whether a value is sensible. If a report needs special wording for zero or blank values, handle that condition separately rather than expecting the format pattern to infer it.

Google Sheets and Excel both provide a TEXT function with similar basic use, but pattern details and locale behavior can differ. This page describes Google Sheets in a web browser. If a formula is moved between programs or shared with people using different locale settings, check the displayed output in the destination spreadsheet rather than assuming every symbol and separator will look identical.

Frequently Asked Questions

What is the syntax for TEXT in Google Sheets?
The syntax is =TEXT(value, format_pattern). The first argument is the number, date, or time to display; the second is a quoted pattern such as "0.00" or "mmmm d, yyyy".

Does TEXT convert a number into text?
Yes. Its result is a text string that looks like the formatted value. Keep the original number in a separate cell if you still need to sum, compare, or otherwise calculate with it.

How do I show two decimal places with TEXT?
Use the pattern 0.00. It displays two decimal places, including trailing zeroes. For example, 4.5 is displayed as 4.50 when the value is formatted with that pattern.

How do I format a date as month and year?
Use a pattern such as mmmm yyyy for a full month name and four-digit year, or mmm yyyy for an abbreviated month. The source must be a date value for TEXT to format it as a date.

Why does TEXT show 2,500% instead of 25%?
The source likely contains 25 rather than 0.25. A percentage pattern treats the stored number as a proportion and scales it for display. Use the value that matches your data convention, or adjust the source before formatting.

Should I use TEXT or the Format menu?
Use the Format menu when you only need to change how a cell looks and want it to remain numeric. Use TEXT when a formula must return a formatted text string, especially when joining a date or number to words.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.