TEXT Function

The TEXT function allows you to format numbers by applying custom formatting codes. It is beneficial when you need to display numbers in a more readable format or when combining numbers with text or symbols in a specific way.

Technical Details

Syntax:

=TEXT(value, format_text)

The TEXT function syntax includes the following arguments:

Argument Name

Description

value

The numeric value that you want to convert into formatted text.

format_text

A text string that defines the format you want to apply to the value.

Overview

In its simplest form, the TEXT function follows this structure:

=TEXT(Value you want to format, "Format code you want to apply")

Example Formulas

  1. Currency Formatting:

    =TEXT(1234.567, "$#,##0.00")

    • Description: Formats the number as currency with a thousands separator and two decimal places. The result is $1,234.57. Note that it rounds the value to two decimal places.

  2. Percentage Formatting:

    =TEXT(0.285, "0.0%")

    Description: Converts the number to a percentage with one decimal place. The result is 28.5%.

  3. Leading Zeros:

    =TEXT(1234, "0000000")

    Description: Adds leading zeros to make the number seven digits long. The result is 0001234.

Using other Functions within the TEXT Function

The TEXT function can also be combined with other functions within the value argument. For example, you can use the GetProperty() function to retrieve a value from a property and then format it using the TEXT function.

Example 

=TEXT(GetProperty("price_property"), "$#,##0.00")

  • Description: In this example, the GetProperty() function retrieves the value from the price_property field. The TEXT function then formats that value as currency with a thousands separator and two decimal places.

Example Formula

When using other functions within the TEXT function, the result of those functions is treated as the value argument. You can apply any valid format to the result, just as you would with a static numeric value.

=IF(IsEmpty(GetProperty("project_sequence")); ""; TEXT(GetProperty("project_sequence"); "00000"))

Explanation

  • Purpose: This formula checks if the project_sequence property is empty. If it is empty, the formula returns an empty string (""). If the property contains a value, the formula applies the TEXT function to format the project_sequence with leading zeros, ensuring that the output is always five digits long.

Example Scenario:

  • Scenario 1: If project_sequence is 78, the formula returns 00078.

  • Scenario 2: If project_sequence is empty, the formula returns an empty string ("").

Key Point:

This formula is useful when you need consistent formatting for numerical sequences, especially when the values may vary in length or may sometimes be empty.

Search