This guide covers everything you need to know about the Google Sheets TEXT function, including its definition, syntax, use cases, and how to use it.
What is the TEXT Function? How Does It Work?
The TEXT function in Google Sheets converts a numeric value into a text string This function is particularly useful when you need to manipulate how the numbers, dates, or times appear in your spreadsheet. The primary purpose of the TEXT function is to showcase data in a more readable, user-friendly format without altering the original data itself.
The TEXT function works by taking two arguments. The first is the numeric value (number, date, or time) you need to format, and the second is the desired format. This desired format must be enclosed in quotation marks. For instance, you might use the TEXT function to format a date in the ‘yyyy-MM’ format or to present a numeric value in a currency format like ‘$0.00’.
It’s worth noting, however, that there are certain limitations and rules when it comes to using this function. For example, the format argument to TEXT cannot contain an asterisk (*). Moreover, the TEXT function does not support the ‘?’ pattern or fractional format patterns in Google Sheets. However, it does support various date and time patterns.
An important point to remember is that date/time patterns and # or 0 cannot be mixed within the same format specification. In other words, you need to ensure that the format you specify matches the type of data you’re applying it to.
The TEXT function can be easily compared to other Google Sheets functions like T, FIXED, and DOLLAR. The T function returns string arguments as text, while the FIXED function formats a number with a chosen number of decimal places, and the DOLLAR function formats a number into the locale-specific currency format. However, the TEXT function is uniquely flexible, allowing you to specify the exact format you want to use.
TEXT Syntax
The syntax and arguments for the TEXT function in Google Sheets are as follows:
TEXT(number, format)
Where:
- “number” is the number, date, or time you wish to format. This can be a direct input (like 1.23 or DATE(1969,7,20)) or a cell reference (like A2).
- “format” is the pattern by which you want to format the number. This argument should be enclosed in quotation marks (like “$0.00” or “#.0?”).
There are some important usage notes related to the syntax and arguments of the TEXT function:
- The “format” argument to TEXT cannot contain an asterisk (*). This means you cannot use “*” to represent any series of characters in your format.
- The TEXT function does not support the “?” pattern in Google Sheets. This means you cannot use “?” to represent optional digits in your format.
- The TEXT function does not support fractional format patterns. This means you cannot use patterns like “#.###” to represent fractions in your format.
- The TEXT function does support various date and time patterns. This means you can use patterns like “yyyy-MM” to represent dates and times in your format.
- Note that the date/time patterns and “#” or “0” cannot be mixed. This means you cannot use a combination of date/time patterns and “#” or “0” in your format.
Examples of How to Use the TEXT Function
Here are some practical examples of how to use the TEXT function in Google Sheets:
Example #1: Formatting Numbers
Let’s assume you have a number in cell A1, say 1234.5678, and you want to format it to have two decimal places. You can use the TEXT function in this way:
=TEXT(A1, “0.00”)
The result will be 1234.57. The TEXT function has rounded the number to the nearest hundredth.
Example #2: Displaying Dates in Different Formats
Suppose you have a date in cell B2, such as 12/31/2021, and you want to display it in a different format. Here’s how you can achieve this using the TEXT function:
=TEXT(B2, “dd-mm-yyyy”)
The result will be 31-12-2021. The TEXT function has changed the date format from mm/dd/yyyy to dd-mm-yyyy.
Example #3: Combining Text with Numbers
Assume you want to combine the text “Order Number: ” with a number in cell C3, which is 567. You can use the TEXT function like this:
=”Order Number: “&TEXT(C3, “0”)
The result will be “Order Number: 567”. The TEXT function has helped to combine the text with the number.
Example #4: Displaying Currency
Let’s say you have a number in cell D4, which represents an amount of money, for example, 1234.56, and you want to display it as currency. You can use the TEXT function in this way:
=TEXT(D4, “$#,##0.00”)
The result will be $1,234.56. The TEXT function has formatted the number as currency.
Example #5: Formatting Percentages
Suppose you have a decimal number in cell E5, say 0.25, and you want to display it as a percentage. Here’s how you can do this using the TEXT function:
=TEXT(E5, “0%”)
The result will be 25%. The TEXT function has converted the decimal number to a percentage.
Why Is TEXT Not Working? Troubleshooting Common Errors
If you are having difficulty with the TEXT function in Google Sheets, several common errors may be at fault. Understanding these errors, their causes, and their solutions can help you troubleshoot and resolve the issues.
#VALUE! Error
Cause: This error is typically returned when the TEXT function doesn’t recognize the format text you’ve entered. The error can occur if the formatted text is not enclosed in quotation marks or the format code is not recognized.
Solution: Make sure that the format text is enclosed in quotation marks. Also, verify that the format codes are correct. For example, a date should be formatted as “dd/mm/yyyy” or “mm/dd/yyyy”.
#N/A Error
Cause: This error occurs when the input cell is empty or the data cannot be found.
Solution: Ensure the cell you reference in your formula contains data. If the input cell is supposed to contain a date or time, verify that it is formatted correctly.
#REF! Error
Cause: This error is returned when an invalid cell reference is used in the formula. This can occur if you’ve deleted or moved the cells the formula references.
Solution: Check your formula to make sure all cell references are valid. You may need to adjust your formula to reference the correct cells if you’ve recently deleted or moved cells.
#NUM! Error
Cause: This error is returned when the formula results in a number too large or too small to be represented in Google Sheets.
Solution: Review your formula to ensure that it doesn’t produce a result that is too large or too small. Adjust your formula or input data to avoid this issue if necessary.
#DIV/0! Error
Cause: This error is returned when a number is divided by zero in your formula.
Solution: Review your formula to ensure that no division by zero is occurring. If necessary, adjust your formula or the input data to avoid this issue.
#NAME? Error
Cause: This error is returned when Google Sheets doesn’t recognize a name or a function in your formula.
Solution: Check your formula for any misspelled function names or misplaced punctuation. Ensure all functions are spelled correctly, and all necessary punctuation is included.
Circular Dependency Detected Error
Cause: This error occurs when the formula references the cell it’s located in, creating a circular reference.
Solution: Review your formula to ensure it doesn’t reference its own cell. If necessary, adjust your formula to avoid a circular reference.
Using TEXT With Other Google Sheets Functions
Combining the TEXT function with other functions in Google Sheets can unlock even more powerful ways to manipulate and display data. Here are a few examples of how TEXT might be used in conjunction with other functions.
With CONCATENATE
The CONCATENATE function combines several text strings into one text string. When used with the TEXT function, it’s possible to integrate numerical values and dates into these strings in any desired format.
Usage: =CONCATENATE(“Total sales for the month: $”, TEXT(A1, “$#,##0.00”))
Example: If cell A1 contains the value 5000.5, this formula would return “Total sales for the month: $5,000.50”.
With DATE
The DATE function converts a year, month, and day into a date. The TEXT function can convert this date into any desired format.
Usage: =TEXT(DATE(year, month, day), “mmmm dd, yyyy”)
Example: =TEXT(DATE(2022, 7, 4), “mmmm dd, yyyy”) would return “July 04, 2022”.
With NOW
The NOW function returns the current date and time. The TEXT function can convert this value into any desired format.
Usage: =TEXT(NOW(), “mmmm dd, yyyy h:mm:ss AM/PM”)
Example: If the current date and time were July 4, 2022, 3:45 PM, this formula would return “July 04, 2022 3:45:00 PM”.
With SUM
The SUM function adds all the numbers in a range of cells. The TEXT function can convert this sum into any desired format.
Usage: =TEXT(SUM(A1:A10), “$#,##0.00”)
Example: If the sum of the numbers in cells A1 through A10 is 5000.5, this formula would return “$5,000.50”.
With AVERAGE
The AVERAGE function calculates the average of the numbers in a range of cells. The TEXT function can convert this average into any desired format.
Usage: =TEXT(AVERAGE(A1:A10), “$#,##0.00”)
Example: If the average of the numbers in cells A1 through A10 is 500.05, this formula would return “$500.05”.
For more details on the TEXT function, check out the official documentation at the Google Docs Editors Help Center.
