The TEXT function makes it easy to custom format cells in a spreadsheet. TEXT converts numbers to text and supports custom format codes that let you customize the formatting applied to each cell. You can custom format numbers, dates, and times and combine with text while maintaining formatting.
The function is particularly useful for things like dynamic dashboards and reports where you want to customize formatting dynamically as new data is added. Row Zero is an enterprise-grade spreadsheet built for big data that makes it easy to apply custom formatting with TEXT. Continue reading to learn how to use TEXT and view TEXT examples.
- What is the TEXT function?
- TEXT custom format codes
- 8 ways to use TEXT to custom format cells
- Why use the TEXT function?
What is the TEXT function?
The TEXT spreadsheet function converts values to text and applies custom formatting using custom format codes. The TEXT function uses the following syntax:
=TEXT(value, format_text)
The 2 arguments are:
- value is the number, date, time, duration or other scalar to format
- format_text is a format pattern such as "$0.00", "dd‑mm‑yyyy", "h:mm AM/PM", etc.
Think of it generally as: =TEXT(value to format, formatting to apply)
Below, you'll find TEXT function examples that demonstrate how to use custom format codes for numbers, dates, and times. You can view these TEXT examples live in a spreadsheet here.
Note: Because the TEXT function converts numbers to text, further calculations will see it as 0. It's best to keep the original numeric or date/time value for further calculations. You can also convert back to numbers using NUMBERVALUE or VALUE functions when needed.
TEXT custom format codes
You can use several custom format codes with TEXT to convert numbers, dates, and times to text and apply precise formatting.
Custom number format codes
Convert numbers to text and apply custom number formatting with the following format codes:
- 0 – Mandatory digit (shows extra leading or trailing zeros)
- '#' - Optional digit (suppresses extra zeros)
- ? – Optional digit that reserves space (aligns fractions)
- ?/? – Converts number to fraction
- , – Thousands separator; each trailing comma divides by 1000
- . – Decimal separator
- % - Append % to multiply by 100 and add the symbol
- E+00 or E-00 - Converts numbers to scientific notation
Custom Number Formatting Examples:
Custom date format codes
Convert dates to text and apply custom date formatting using the following format codes:
- d – Day without leading zero (18)
- dd – Day with leading zero (04)
- ddd – Abbreviated weekday (Fri)
- dddd – Full weekday (Friday)
- m – Month number (1–12)
- mm – Month with leading zero (04)
- mmm – Abbreviated month name (Apr)
- mmmm – Full month name (April)
- yy – 2‑digit year (25)
- yyyy – 4‑digit year (2025)
Custom Date Formatting Examples:
Custom time format codes
Convert times to text and apply custom time formatting using the following format codes:
- h / hh – Hours 0–23 (6 / 06)
- m / mm – Minutes (3 / 03)
- s / ss – Seconds (9 / 09)
- AM/PM – 12‑hour clock suffix (6:00 PM)
- [h] / [mm] / [ss] – Elapsed hours, minutes or seconds beyond the normal range
Note: The TEXT function uses 'm' for both month and minute. TEXT distinguishes between month and minute based on context — specifically whether you're formatting a date or a time. To ensure 'm' is interpreted as minute instead of month, use it in tandem with hour (e.g. "hh:mm"") or use the TIME function when specifying your value (e.g. =TIME(7,11,16))
Custom Time Formatting Examples:
Custom Format Elapsed Time Examples:
8 ways to use TEXT function to custom format cells
Here are a few TEXT examples for custom formatting cells. Explore live in a spreadsheet here.
Adding units to numbers (currency, temperature, weight, etc.)
Combine text with formatted numbers and dates
If you use CONCAT to combine text with numbers and dates, the numbers lose formatting and the dates get converted to a number. The TEXT function maintains number formatting in text and lets you format dates with text to create readable labels or summaries.
Maintain leading zeros for IDs or ZIP codes
Sometimes leading zeros are dropped when working with ZIP codes or ID numbers. You can use the TEXT function to maintain leading zeros in ZIP codes or format ZIP + 4 codes.
You can also use TEXT to apply consistent formatting and trim numbers to the same length. This can also be helpful to generate invoice numbers or customer IDs.Dynamic dashboard labels
When working with connected spreadsheets and dynamic dashboards, TEXT lets you customize formatting dynamically as new data is added.
Extract and format day of week and month names
Language localization (e.g. localize date and number formats)
Phone number formatting
Convert values to text for lookups or matching
Ensure formatting consistency when using XLOOKUP, VLOOKUP, MULTILOOKUP, or joins. Lookups won't work if formatting is inconsistent. This is helpful when looking up unique IDs, phone numbers, ZIP codes, etc.
Why use the TEXT function?
The TEXT function is useful for converting numbers, dates, and times into custom-formatted text strings. This lets you have precise control over how data is displayed and presented. It is especially powerful when building dynamic labels, creating readable summaries, or formatting dynamic dashboards in a spreadsheet. By applying custom formatting codes, you can display values as currency, percentages, dates, etc. and maintain number and date formatting within text. The TEXT function makes raw data more human-readable and presentation-ready. Explore live TEXT function examples here in Row Zero, an enterprise-grade spreadsheet built for big data and dynamic analysis.