Check Sheet Logo
Check Sheet

TEXT

The TEXT formula 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.

Usage

Use the TEXT formula with the syntax shown below, it has 2 required parameters:

=TEXT(number, format)
Parameters:
  1. 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.
  2. format (required):
    The format that you want to apply to the number. The format can include any combination of text, numbers, and special characters.

Examples

Here are a few example use cases that explain how to use the TEXT formula in Google Sheets.

Formatting currency

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

The 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.

Displaying percentages

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.

Common Mistakes

TEXT not working? Here are some common mistakes people make when using the TEXT Google 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.

Related Formulas

The following functions are similar to TEXT or are often used with it in a formula:

  • VALUE

    The VALUE formula 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.

  • ROUND

    The ROUND formula 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 places parameter is not specified, the formula rounds to the nearest integer.

  • SUM

    The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

  • IFERROR

    The IFERROR formula 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.

Learn More

You can learn more about the TEXT Google Sheets function on Google Support.