TEXTformula is used to format a number into text using a specified format. The format can include special characters such as currency symbols, decimal points, and thousands separators. This formula is useful when you need to display numbers as text in a specific format.
- How to use
- Examples of using
TEXTformula not working?
- Similar formulas to
TEXT formula with the syntax shown below, it has 2 required parameters:
- number (required):
The number that you want to format as text. It can be a number or a reference to a cell that contains a number.
- format (required):
The format that you want to apply to the number. The format can include any combination of text, numbers, and special characters.
ExamplesHere are a few example use cases that explain how to use the
TEXTformula in Google Sheets.
You can use the
TEXT formula to format a number as currency. For example,
=TEXT(A2, "$#,##0.00") will format the number in cell A2 as currency with two decimal places.
Creating custom date formats
TEXT formula can also be used to format a date in a custom format. For example,
=TEXT(A2, "yyyy-mm-dd") will format the date in cell A2 as year-month-day.
You can use the
TEXT formula to format a decimal number as a percentage. For example,
=TEXT(A2, "0.00%") will format the number in cell A2 as a percentage with two decimal places.
TEXTnot working? Here are some common mistakes people make when using the
TEXTGoogle Sheets Formula:
Using the wrong format code
One common mistake is to use the wrong format code when converting a number to text. Make sure to use the correct codes for the format you want to display.
Not using quotes around the format code
Another mistake is to forget to enclose the format code in quotes. This will cause an error in the formula and prevent it from working correctly.
The following functions are similar to
TEXT or are often used with it in a formula:
VALUEformula in Google Sheets converts a text representation of a number to an actual number. This formula is most commonly used when you have imported data from another source that is formatted as text, but you need to use it in a numerical calculation.
ROUNDformula rounds a number to a specified number of decimal places. It is commonly used to simplify large numbers or to make a number more readable. The formula can round both positive and negative numbers. If the
placesparameter is not specified, the formula rounds to the nearest integer.
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
IFERRORformula is used to check whether a specified value results in an error or not. If the value results in an error, then it returns a user-specified value instead of the error. This function is commonly used to prevent errors from breaking a formula or to replace error messages with custom messages.
You can learn more about the
TEXT Google Sheets function on Google Support.