Normally I am using the Text Function in Google Sheets to change the date format. But you can format number and time too using this function.
Also, knowingly or unknowingly, you are using the Text function in Google Sheets Query. Formatting the date criterion to text is a must in Query.
This spreadsheet tutorial mainly focuses on the use of Google Sheets Text function in number, time and date formatting.
The use of the Text function in Query formula is detailed in another tutorial. Of course, I’ll provide you the link to that tutorial below.
Syntax:
TEXT(number, format)
Purpose of Google Sheets Text Function
The one and only purpose of the Text function is to covert a provided number into a text as per the specified format.
Having said that I’m starting the example section. Let’s start with some basic example of the use of the function Text in Google Sheets.
Examples of the Use of the Text Function
Before starting the example, you should clearly understand the function arguments. What are they?
Just see the syntax again. The first argument is “number” in that. No doubt it’s the “number” that you want to convert to text and then format. The number can be number, date or time.
The second argument is the “format”. It’s the important part. When you want to format a number, you may want to use 0 or # in the format. Also you can use both of them together. The difference I’ll explain to you below.
Examples of the Text function in Google Sheets
Number:
See the difference in the text formula output when using 0 and the # format.
=text(25.5,"0.00")
Result: 25.50
=text(25.5,"#.##")
Result: 25.5
The former forces the display of zeros but the latter does not.
How to force preceding zeros to a number in Google Sheets? See the example.
The text formula in cell D1 tests the value in cell A1. If the value in cell A1 is a string, the same value is returned. If the value in A1 is a number, the formula ensures that the number contains 6 digits else it pads the number with zeros.
I’ve used the ISNUMBER function to test whether the value in A1 is a number. See the values in row # 2, 3 and 4 and the result in the corresponding column D. I’ve copied the formula in D1 to the rows down.
Date Formatting Using the Text Function:
The Text function in Google Sheets has much use in date formatting. Further, it’s a must in Query.
=TEXT("31/12/2018", "mmm-d-yyyy")
Result: Dec-31-2018
=TEXT("31/12/2018", "mmmmm d,yyyy")
Result: D 31,2018
In date format, you should know the following date patterns.
The use of the Text function in the Query can’t be explained in few lines. So here is the link to that individual tutorial to learn the usage in detail.
1. How to Use Date Criteria in Query Function in Google Sheets
Time Formatting:
Here are two Time-related Text formulas to make you understand how to format time in Google Sheets.
=text("10:15","HH:MM:SS")
Result: 10:15:10
The below text formula converts a 24 hours time format to 12 hours AM/PM format.
=text("22:25:10","HH:MM:SS AM/PM")
Result: 10:25:10 pm
Now try this formula to format current date and time to time format with MilliSeconds.
=text(now(),"HH:MM:SS.000")
Finally here is one more use. I am combining text with a date. See the difference in the formula output when with and without using the Text Function in Google Sheets.
Formatting of Text and Date Combination:
For this test, the value in cell A1 is the date “31/05/1983” and in B1 the string “John”.
="Date of Birth of "&B1&" is "&A1
Result: Date of Birth of John is 30467
="Date of Birth of "&B1&" is "&text(A1,"DD/MM/YYY")
Result: Date of Birth of John is 31/05/1983
Similar: Combine Text and Date in Google Doc Spreadsheet Using Formula
That’s all.
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?
Hi, Joe Yoder,
I’m not familiar with Apps Script 🙁
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
:00in 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!
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,
Explained the Formula Here:
https://infoinspired.com/google-docs/spreadsheet/hoconcatenate-start-time-with-end-time-in-google-sheets/
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.
Hi, sh in sf,
Seems this formula works.
=text(C1,"# (###) ###-####")and this too.
=text(C1,"? (000) 000-0000")Best,
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
Hi,
I am clueless. It should work in the normal case.