Skip to main content

Supported Numeric Formatting

The following formatting can be applied to Integer and Decimal types or to String values that are being converted to numeric types.

Tip

Designer Cloud Powered by Trifacta Enterprise Edition supports Java number formatting strings, with some exceptions.

Supported Key Codes

Code

Description

Example Format String

Example Inputs

Example Outputs

#

Insert a digit if it is present in the data.

'###,###'

99

999

1000

10000

99

999

1,000

10,000

0

Indicate required digits. If a digit is not available in the source, inserts zero in the data.

'00.##'

20

7.1

20.00

07.1

$

You can add constants values to the expression. For example, you can insert currency markers at the beginning of your expression.

Note

The following currency formats are supported: $", "€", "£", "¥", "₩", "₹", "NT$", "R$", "R", "Rs", "Kr

Whitespace is respected, except in the following case.

'$ ##.##'

20

2514.22

6.6666

$ 20

$ 2514.22

$ 6.67

(space)

You can use space as a grouping separator. When space is used to group sets of digits, all other whitespace in the value is trimmed.

'$ ###.##' where space is used as grouping separator.

123456.78

£ 123456.78

$123 456.78

$123 456.78

%

Percentage expressions can be at the back of the number formatting expression.

Note

When the percentage sign is added to the format string, the value is automatically multiplied by 100. When the format string is used with the NUMVALUE function, the value is automatically divided by 100 to return the decimal value.

'##.## %'

0.20

14.22

6.6666

20 %

1422 %

666.67 %

-

Negative value indicators can be added to the front part of the number formatting string.

  • Negative value indicators at the end of the number are not supported.

  • If the source value is positive, the negative value is rendered.

    Note

    In this case, the source value is formatted to appear as its opposite.

  • If the source value is negative, a second dash is added to the front of the value. See examples.

    Note

    In this case, the value is formatted as a non-numeric value. You can add a second step to your recipe to remove the second dash from column values.

Note

After the formatting has been applied, type inference may be re-applied to the column, which can change the data type of the column.

'-###,###.00'

123

-123

1234.56

-1234.56

-123.00

--123.00

-1234.56

--1234.56

Key Codes as Separator Values

Some functions support the use of specifying key codes for grouping and decimal separators:

  • NUMFORMAT Function Formats a numeric set of values according to the specified number formatting. Source values can be a literal numeric value, a function returning a numeric value, or reference to a column containing an Integer or Decimal values.

  • NUMVALUE FunctionConverts a string formatted as a number into an Integer or Decimal value by parsing out the specified decimal and group separators. A string or a function returning formatted numbers of String type or a column containing formatted numbers of string type can be inputs.

Note

Separators must be specified when using the NUMVALUE function.

Separators for locales

Grouping and decimal separators can be used to format values for specific locales. Below, you can see how you can format values for locales.

Example Locale

Grouping Separator

Decimal Separator

Example Formatting

Example Output

U.S locale

Comma (,)

Period (.)

NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00',',','.')
1,000,000.01

Spanish locale

Period (.)

Comma (,)

NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00','.',',')
1.000.000,01

French locale

Space

Comma (,)

NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00',' ',',')
1 000 000,01

Example Separators

Input

Example Format String

Grouping Separator

Decimal Separator

Output

123.45

##.00

,

.

123.45

123.4

##.00

,

.

123.40

1234

#,###

,

.

1,234

1234.5

#,###.#

,

.

1,234.5

1234.56

#,###.##

,

.

1,234.56

1234

###,#

.

,

1.234

1234.56

###,#

.

,

1.234,56

1234

#,##

.

,

1.234

1234

#.###,0

.

,

1.234,0

123.45

##,#

space

,

123,45

1234

# ###

space

,

1 234

1234.5

# ###,#

space

,

1 234,5

1234.56

# ###,##

space

,

1 234,56