SheetsdrillTEXT
TEXT(number, format) converts a number or date into text in the format you specify, such as TEXT(A2, "mmm yyyy") for Mar 2026 or TEXT(1234.5, "$#,##0.00") for $1,234.50. Use it when a value is going into a label, a concatenation or a QUERY string and must look a certain way.
Syntax
=TEXT(number, format)| Part | What it is |
|---|---|
number | The number, date or time to format. |
format | A format code in quotes: 0, 0.00, #,##0, 0%, $#,##0.00, 000, yyyy-mm-dd, mmm yyyy, dddd, hh:mm. |
Examples
=TEXT(DATE(2026,3,14), "mmm yyyy")Result: Mar 2026
Use "mmmm" for March and "yyyy-mm" for 2026-03, which sorts correctly.
=TEXT(0.256, "0.0%")Result: 25.6%
=TEXT(7, "000")Result: 007
Common mistakes
- The result is text, so it will not sum or sort as a number; keep the raw value in another column for calculations.
- Month labels like Jan, Feb sort alphabetically; group by TEXT(A2, "yyyy-mm") instead when order matters.
- Format codes use m for month and mm for minutes only when hours are present; "mm" alone is month.
Practice TEXT
These drills check your answer on live data, free, no account needed.
- Month label with TEXTTurn each invoice date into a label such as "March 2025": full month name, a space, four-digit year. Fill B2 and B3 using TEXT.
Questions
How do I get the month name from a date?
=TEXT(A2, "mmmm") for the full name or "mmm" for the short one.
How do I add leading zeros?
=TEXT(A2, "00000") pads to five digits, or format the column with a custom number format if you want to keep it numeric.
Related
- CONCATENATE and &How to combine cells in Google Sheets with CONCATENATE, CONCAT and the & operator, add spaces and separators, keep number and date formats, avoid mistakes.
- DATE, YEAR, MONTH and DAYHow DATE, YEAR, MONTH and DAY work in Google Sheets to build a date from parts, pull the year or month out of a date, group by month and fix text dates.
- ROUNDHow ROUND, ROUNDUP, ROUNDDOWN and MROUND work in Google Sheets, with examples for two decimals, thousands and nearest 0.05, and why formatting does not round.
- TODAY and NOWHow TODAY and NOW in Google Sheets return the current date and time, how to calculate days until a deadline, age and overdue flags, and why the value changes.