Google Sheets handles dates and numbers using strict internal logic. A date like “October 31, 2024” is actually stored as the serial number 45596. While this is great for performing math (like calculating days between two dates), it becomes a nightmare when you need to combine that date with regular text in a sentence.
If you type the formula ="The project is due on " & A2 (where A2 is a date), Google Sheets will output: “The project is due on 45596”.
To force the spreadsheet to display the date as human-readable text exactly the way you want it, you must use the TEXT function. In this guide, you will learn how to use the TEXT function to format dates and numbers seamlessly.
The TEXT Syntax
The TEXT function acts as a translator. It takes a raw number and applies a specific visual format to it before converting it into a hard text string.
=TEXT(number, format)- number: The cell containing the raw date or currency value.
- format: A specific code, wrapped in quotation marks, that dictates exactly how it should look.
Use Case 1: Formatting Dates Inside Sentences
Let’s fix the broken sentence from earlier. Cell A2 contains the date (10/31/2024). We want the sentence to read: “The project is due on Oct 31, 2024.”
To achieve this, we wrap cell A2 in the TEXT function and apply a date format code:
="The project is due on " & TEXT(A2, "mmm dd, yyyy")The mmm outputs a three-letter month (Oct), dd outputs a two-digit day (31), and yyyy outputs the four-digit year.
Common Date Format Codes
You can mix and match these codes inside the quotation marks to create any layout you need:
- d: Day without leading zero (5)
- dd: Day with leading zero (05)
- ddd: Short day of the week (Mon)
- dddd: Full day of the week (Monday)
- m: Month without leading zero (8)
- mm: Month with leading zero (08)
- mmm: Short month name (Aug)
- mmmm: Full month name (August)
- yy: Two-digit year (24)
- yyyy: Four-digit year (2024)
If you just want to extract the day of the week from a date to see if someone submitted a form on a weekend, you can simply use: =TEXT(A2, "dddd"), which will output “Saturday”.
Use Case 2: Forcing Leading Zeros on Numbers
The TEXT function is not just for dates. It is essential when dealing with numeric ID codes or ZIP codes.
By default, if you type “00123” into a cell, Google Sheets assumes it is a math number, deletes the leading zeros, and displays “123”. This breaks VLOOKUPs and database imports.
You can use the TEXT function to force a specific number of digits, padding the front with zeros if necessary.
=TEXT(A2, "00000")If A2 contains “123”, this formula forces it to be a 5-digit text string, outputting 00123.
Use Case 3: Formatting Currency in Sentences
Similar to dates, if you combine a dollar amount with text, it loses its formatting. ="Your total is " & A2 (where A2 is $1,500.50) will output “Your total is 1500.5”.
Use TEXT to restore the currency layout:
="Your total is " & TEXT(A2, "$#,##0.00")This code ensures the output always has a dollar sign, a comma separator for thousands, and exactly two decimal places.
By mastering the TEXT function and its format codes, you gain absolute control over how your data is presented, allowing you to build automated, highly readable reports.