How to Use the TEXT Function in Google Sheets

The purpose of the TEXT function is to convert a number (including dates and times, as dates in Google Sheets are stored as serial numbers) into text in the specified format.

You will find the TEXT function particularly useful when you need to combine text with numbers while preserving specific number formatting.

However, remember that the output of the TEXT function is always text. If you want to format a number and retain its numeric properties, use the formatting options available under the Format menu.

With that clarified, here is the syntax of the TEXT function in Google Sheets:

TEXT Function: Syntax and Parameters

Syntax:

TEXT(number, format)

Arguments:

number: The number to format (can also be a date, time, or datetime).
format: The pattern to format the number. This must be enclosed in double quotes.

Let’s explore the key formatting patterns used with the TEXT function. These patterns are the foundation of the function’s versatility.

Understanding the Formatting Patterns in the TEXT Function

I’ve used a simple and easy-to-follow approach to explain the formatting patterns in the TEXT function, making them easier for you to remember in the future.

0 (Zero) and # (Hash)

The 0 pattern forces zeros to appear if a number has fewer digits than specified.
This is useful for adding leading zeros or ensuring consistent decimal places.

Examples:

=TEXT(1, "00") // returns "01"
=TEXT(10, "00") // returns "10"
=TEXT(0.5, "0.00") // returns "0.50"

The # pattern behaves similarly but does not force zeros for whole numbers or fractional parts.

Real-Life Example:

Assume cell A1 contains 123456789.25. Here’s how different patterns format the number:

=TEXT(A1, "$#,##0.00") // returns "$123,456,789.25"
=TEXT(A1, "$#,###.##") // returns "$123,456,789.25"
=TEXT(A1, "$#,###.00") // returns "$123,456,789.25"

If A1 contains 50.2, the results will be $50.20, $50.2, $50.20 (respectively).

Explanation of the Patterns:

$: Adds a currency symbol.
#,##0 | #,###: Ensures thousands are separated by commas. The 0 forces zeros, while # does not. The zero is especially useful when dealing with fractional numbers (e.g., 0.25), as it ensures a leading zero appears, resulting in $0.25 instead of $.25.
00: ##: Adds two decimal places; 0 forces digits, while # does not.

Date and Time Patterns in the TEXT Function

The TEXT function is widely used to format dates and times exactly as you want. Let’s look at a few examples to understand how date and time patterns work.

Date Patterns

Suppose A1 contains the date 26/02/2024. We use:

d: Day
m: Month
y: Year

Examples:

=TEXT(A1, "D-M-Y") // returns "26-2-24"
=TEXT(A1, "DD-MM-YY") // returns "26-02-24"
=TEXT(A1, "DDD-MMM-YY") // returns "Mon-Feb-24"
=TEXT(A1, "DDDD-MMMM-YYYY") // returns "Monday-February-2024"

Exceptions:

MMMMM: Returns the first letter of the month.
YYY: Still returns a four-digit year.

Notes:

You can replace - with / or any separator you prefer.
Date patterns are case-insensitive.

Time Patterns

HH: Hours in a 24-hour clock.
MM: Minutes.
SS: Seconds.

To switch to a 12-hour clock, add AM/PM to the pattern.

Examples:

Suppose A1 contains 10:10.

=TEXT(A1, "HH") // returns "10"
=TEXT(A1, "HH:MM") // returns "10:10"
=TEXT(A1, "HH:MM:SS") // returns "10:10:00"
=TEXT(A1, "HH:MM:SS AM/PM") // returns "10:10:00 AM"

Combining Date and Time

Here is how to format a datetime returned by NOW() using the TEXT function in Google Sheets:

=TEXT(NOW(), "DD/MM/YYYY HH:MM:SS AM/PM")

This formula returns the result in the format 02/12/2024 07:26:58 AM. (Note: Since NOW() is volatile, the result updates whenever the sheet recalculates.)

Important Notes

Here are some key points to remember when using the TEXT function in Google Sheets:

The output of the TEXT function is text, not a number or date.
Never mix number (0, #) and date/time patterns in a single formula.
The TEXT function in Google Sheets does not support wildcard characters like ? and *.

Resources

9 COMMENTS

Joe Yoder September 8, 2021 At 8:48 pm

I like the capabilities of the text function in Google Sheets but have not been able to make it work in Scripts.

I just get a message “ReferenceError: text is not defined” when I try to use it.

Is the text function supposed to work in Sheets Scripts?

- Prashanth September 11, 2021 At 11:05 am
  
  Hi, Joe Yoder,
  
  I’m not familiar with Apps Script 🙁
  
Events Manager January 14, 2020 At 12:40 am

Hi, Prashanth,

Thanks for all this great info. I’m not sure if what I’m trying to do can be done in one formula or if it will take a few steps.

I have events being submitted by a Google form and would like to merge the time data from the two cells (start time and end time) to a very specific format in a single cell.

You’ll see below, the format changes if both times are in the morning or afternoon, if they span noon, or if there are minutes to be included.

If it’s on the hour, I don’t want :00 in the formatting and the a.m. or p.m. needs to be the lower case with the periods. Examples:

– If the event starts and ends on the hour and in the same half of the day: 9-11 a.m. OR 6-8 p.m.
– If the event spans noon and is on the hour: 9 a.m.-3 p.m.
– If the event start or end time is during the hour: 9:30-11:45 a.m. OR 8:45 a.m.-4:15 p.m.
– Also, some of the above mixed: 9:15 a.m.-3 p.m. OR 10-11:30 a.m. OR 11 a.m.-3:45 p.m.

I plan to retain the original data columns for sorting.

Thanks for considering if you’re willing to tackle this!

- Prashanth January 14, 2020 At 10:04 am
  
  Hi,
  
  I could understand that you wish to combine start and end time with some custom Text formatting. See my attempt.
  
  The formula (array formula which will automatically cover A2:B) for cell D2:
  
  =ArrayFormula(if(A2:A="",,(if(regexreplace(text(A2:A,if(minute(A2:A)=0,"HH am/pm","HH:MM am/pm"))&" - "&text(B2:B,if(minute(B2:B)=0,"HH am/pm","HH:MM am/pm")),"[0-9:]+","")=" am - am",regexreplace(text(A2,if(minute(A2:A)=0,"HH am/pm","HH:MM am/pm"))&" - "&text(B2:B,if(minute(B2:B)=0,"HH am/pm","HH:MM am/pm")),"am.",""),text(A2:A,if(minute(A2:A)=0,"HH am/pm","HH:MM am/pm"))&" - "&text(B2:B,if(minute(B2:B)=0,"HH am/pm","HH:MM am/pm"))))))
  
  When using this formula in a second tab include the sheet tab name with the cell reference like sheet1!A2:A instead of A2:A.
  
  Best,
  
- Prashanth February 5, 2020 At 11:12 am
  
  Explained the Formula Here:
  
  https://infoinspired.com/google-docs/spreadsheet/hoconcatenate-start-time-with-end-time-in-google-sheets/
  
sh in sf August 25, 2019 At 10:27 am

How do I format a number as a US phone number using TEXT

for example, format 11234567890 as 1 (123) 456-7890

I tried

=text(C1,"?" ("000") "000"-"0000")

But getting a syntax error message.

- Prashanth August 25, 2019 At 9:37 pm
  
  Hi, sh in sf,
  
  Seems this formula works.
  
  =text(C1,"# (###) ###-####")
  
  and this too.
  
  =text(C1,"? (000) 000-0000")
  
  Best,
  
Claus Lee November 23, 2018 At 8:14 pm

Hi,

So I have a real problem…

Whatever I do, the text function won’t work.

I insert this for test:

=text(25.5,"0.00")

And all I get is #error

- Prashanth November 24, 2018 At 9:31 am
  
  Hi,
  
  I am clueless. It should work in the normal case.