TEXT function in Google Sheets: format codes for numbers, dates and times
TEXT turns a number, date or time into text in the format you specify. This guide lists the format codes for numbers, currency, percentages, dates and times, shows how to join formatted values with text, and explains the traps: the result is text, and mm means month or minutes depending on its neighbours.
The TEXT function converts a number, a date or a time into text, written in the format you give it:
=TEXT(1234.5, "#,##0.00")
The formula returns 1,234.50. The first argument is the value, the second the format code in quotes.
| Value | Formula | Result |
|---|---|---|
| 1234.5 | =TEXT(A1, "0.0") |
1234.5 |
| 1234.5 | =TEXT(A1, "#,##0.00") |
1,234.50 |
| 1234.5 | =TEXT(A1, "$#,##0.00") |
$1,234.50 |
| 0.256 | =TEXT(A1, "0%") |
26% |
| 0.256 | =TEXT(A1, "0.0%") |
25.6% |
| 5 | =TEXT(A1, "000") |
005 |
| October 5, 2024 | =TEXT(A1, "yyyy-mm-dd") |
2024-10-05 |
| October 5, 2024 | =TEXT(A1, "dddd, mmmm d, yyyy") |
Saturday, October 5, 2024 |
| 2:05:09 PM | =TEXT(A1, "h:mm AM/PM") |
2:05 PM |
| 2:05:09 PM | =TEXT(A1, "hh:mm:ss") |
14:05:09 |
The main use of TEXT is joining a formatted value with other text, as in ="Total: " & TEXT(B2, "$#,##0.00"). To change only how a number looks in its own cell, use Format > Number instead: that keeps the value a number.
TEXT syntax
=TEXT(number, format)
- number: the number, date or time to format, or a cell that holds it.
- format: the format code, in quotation marks.
The result is always text. It is left-aligned, and functions such as SUM skip it.
Format codes
Numbers
| Code | Meaning | Example |
|---|---|---|
0 |
A digit that is always shown, with zeros added if needed | 5 with “000” gives 005 |
# |
A digit that is shown only if it exists | 5 with “#,##0” gives 5 |
. |
Decimal point | 1234.567 with “0.00” gives 1234.57 |
, |
Thousands separator between digits | 1234.5 with “#,##0” gives 1,235 |
, at the end |
Divides by a thousand for each comma | 1234567 with “0.0,,” gives 1.2 |
% |
Multiplies by 100 and adds the sign | 0.256 with “0%” gives 26% |
$ and other symbols |
Shown as typed | 1234.5 with “$#,##0.00” gives $1,234.50 |
; |
Separates the format for positive and negative numbers | -5 with “0.0;(0.0)” gives (5.0) |
TEXT rounds to the number of decimals in the code: 1234.567 with “0” gives 1235.
Dates
| Code | Meaning | Example for October 5, 2024 |
|---|---|---|
d, dd |
Day of the month, without or with a leading zero | 5, 05 |
ddd, dddd |
Name of the weekday, short or full | Sat, Saturday |
m, mm |
Month number, without or with a leading zero | 10, 10 |
mmm, mmmm |
Name of the month, short or full | Oct, October |
yy, yyyy |
Year with two or four digits | 24, 2024 |
Times
| Code | Meaning | Example for 2:05:09 PM |
|---|---|---|
h, hh |
Hour, without or with a leading zero | 14, 14 |
mm after an hour code |
Minutes | 05 |
ss |
Seconds | 09 |
AM/PM |
12-hour clock with the marker | 2:05 PM |
The code mm is the month when it stands alone or next to a day or a year, and minutes when it follows h or comes before ss. =TEXT(A1, "mm") on a date-time returns the month.
To put letters into a format, escape each of them with a backslash or keep the text outside the function: =TEXT(A1, "h") & " hours".
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Examples of the TEXT function
A fixed number of decimals
=TEXT(C3, "0.0")
Every price is shown with exactly one decimal place.
A date in another format
=TEXT(C3, "mm/dd/yyyy")
The date keeps its value; only the way it is written changes. For a format that sorts correctly as text, use “yyyy-mm-dd”.
Currency
=TEXT(C3, "$#,##0.00")
Percentages
=TEXT(C3, "0%")
The value is multiplied by 100, so 0.1 becomes 10%. For one decimal, use “0.0%”.
Time
=TEXT(C3, "h:mm AM/PM")
Join formatted values with text
Join formatted values with text
When a number or a date is joined with text by an ampersand, it loses its format: a date turns into its serial number, a price loses its decimals. TEXT keeps the format inside the sentence.
A label before a number
="Order ID: " & TEXT(B3, "0")
A unit after a number
=TEXT(C3, "###0") & " units"
A sentence with CONCATENATE
=CONCATENATE("Total purchases for ", C3, ": $", TEXT(D3, "#,##0.00"))
- “Total purchases for “, C3, “: $”: fixed text and the product name.
- TEXT(D3, “#,##0.00”): the amount with a thousands separator and two decimals.
More on joining text in the guide to CONCATENATE and the ampersand.
TEXT with other functions
DATE: a date written out
=TEXT(DATE(2024, 10, 15), "mmmm dd, yyyy")
DATE builds the date and TEXT writes it as “October 15, 2024”.
NOW: the current date and time in your own format
=TEXT(NOW(), "mmmm dd, yyyy h:mm:ss AM/PM")
NOW is recalculated whenever the sheet changes, so every cell with this formula shows the same, current moment. It is not a way to record when a row was added.
SUM and AVERAGE: a formatted total
=TEXT(SUM(D3:D12), "$#,##0.00")
=TEXT(AVERAGE(D3:D12), "$#,##0.00")
Use this form when the total goes into a sentence or a label. If the cell is used in further calculations, leave the plain SUM or AVERAGE there and apply a number format to the cell.
Traps and errors with TEXT
⚠️ Error: A column of TEXT results adds up to 0.
✅ Solution: TEXT returns text, and SUM ignores text. Add up the original numbers, or convert the results back with VALUE: =VALUE(TEXT(A1, "0.0")).
⚠️ Error: The month appears where minutes were expected, or the other way round.
✅ Solution: mm is minutes only next to an hour or a seconds code. Write “h:mm” for hours and minutes and “mm/dd” for month and day.
⚠️ Error: The format is ignored, for example =TEXT(1234.5, 0.0) returns 1235.
✅ Solution: The format code is not in quotation marks, so Sheets reads it as a number. Write "0.0".
⚠️ Error: #NAME? or a formula parse error.
✅ Solution: A format code with letters was typed without quotes, for example =TEXT(A1, dd/mm/yyyy). Put the code in straight double quotes.
⚠️ Error: A date shows as a number such as 45570 inside a sentence.
✅ Solution: The date was joined to text without TEXT. Wrap it: ="Due: " & TEXT(A1, "mmm d, yyyy").
⚠️ Error: The formatted value does not change when the text is not a number.
✅ Solution: TEXT formats numbers, dates and times. A text value passes through unchanged: =TEXT("abc", "0") returns abc. For upper or lower case use UPPER, LOWER and PROPER.
Related Google Sheets functions
Related Google Sheets functions
Functions that are often used next to TEXT, each with its own guide:
- CONCATENATE and the ampersand: Join text and values.
- JOIN and TEXTJOIN: Join a range with a separator.
- Dates and times: How Sheets stores dates and times.
- DATE functions: DATE, EDATE, DATEVALUE and DATEDIF.
- NOW and TODAY: The current date and time.
- LEFT/RIGHT/MID: Extract part of a text.
- UNIQUE: Returns the distinct values of a range.
- QUERY date filtering: Uses TEXT to build date literals for a query.
Formatting belongs at the end of the line
Formatting belongs at the end of the line
TEXT is the last step of a report: it decides how a number looks to the reader. The steps before it, where the number came from and whether it is the same number as in last week’s report, cannot be fixed with a format code. A total written as “$4,500.00” looks equally convincing whether the rows under it are current or three exports old.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The values come from the same data mart on every refresh, and TEXT does the part it is meant for: presenting them. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
What are the limitations of the TEXT function in Google Sheets?
The result of TEXT is text, not a number: it is left-aligned, SUM ignores it, and it has to be converted back with VALUE before further calculations. A format code that is not in quotes is read as a number and ignored. And mm means month or minutes depending on the codes around it.
How does the TEXT function handle currency formatting?
The TEXT function handles currency formatting by using specific format codes, such as "€#,##0.00" for Euros or "$#,##0.00" for US dollars. For example, =TEXT(A1, "$#,##0.00") formats the value in A1 as currency with two decimal places, including the chosen currency symbol.
Can I use the TEXT function to format dates and times?
Yes, you can use the TEXT function in Google Sheets to format dates and times. By specifying the appropriate format codes, such as "dd-mm-yyyy" for dates or "hh:mm:ss" for times, you can convert date and time values into text in your desired format.
How to convert a formula to text?
To convert a formula result to text in Google Sheets, wrap the formula with the TEXT function and specify a format. For example, =TEXT(SUM(A1:A10), "0") converts the sum of A1 to A10 into plain text. For a straightforward conversion without formatting, use =TO_TEXT(formula).
What is a TEXT formula?
The TEXT formula in Google Sheets is used to convert a numeric value into text and format it according to specified criteria. It takes the form =TEXT(value, "format_code"), where value is the number you want to format and "format_code" dictates the text format, such as date or currency.
How to add text to a function in google sheets?
To add text to a function in Google Sheets, concatenate the text using the & operator. For example, use =A1 & " additional text" to append text to the content of cell A1. You can also include text directly within a function, such as =CONCAT("Start: ", A1).
How to use the TEXT function in google sheets?
To use the TEXT function in Google Sheets, enter =TEXT(value, "format") into a cell. Replace value with the cell you want to format and "format" with the formatting code, such as "dd-mm-yyyy" for dates or "#,#0.0" for numbers. This converts the value into a specified text format.
How can I combine the TEXT function with other functions for advanced formatting?
Combine the TEXT function with others like CONCAT or & to format and merge data dynamically. For example, format a date with TEXT and combine it with text: ="Date: "&TEXT(A1, "dd-mmm-yyyy"). This approach ensures consistency while presenting complex data in readable formats.
