View All Docs
Hide Navigation
Product overview
Account overview
Integrations
Transforms
Security
Transforms   ->

Format numbers

The Format numbers step can reformat numbers across multiple columns in different formats. It can control numeric formatting options, such as, commas, currencies, rounding and percentages.

Input/output

Let's use some of our store's sales information as the input data.

In this example, we'll want to reformat the numbers displayed in the columns "Sale Price" and "Refunded Price" to an accounting format with currency and proper decimal points.

After connecting this data into the Format numbers step, we select the column(s) we'd like to apply the formatting rules to by clicking the plus button. You can either choose to Format these columns to specify the columns you'd like to apply formatting to, or you can choose to Format all columns except to specify the columns you'd like to exclude from formatting.

Next, you can either select from the pre-set options in the dropdown or click the ... button to select from the following options:

  • Numbers
  • Percents
  • Currency
  • Accounting

By switching the default "Numbers" setting to "Accounting," our output data is updated with $ symbols, 2 decimal places, and parentheses wrapping negative numbers.

Default settings

When you first connect your data into the Format numbers step, it will auto-select the first column with numeric values that appears in the data set. It will automatically apply the "Numbers" formatting, which displays numbers  with comma groupings, 2 # Decimals, and round to the nearest. The below screenshot shows us an updated "Variant ID" column after those settings automatically formatted it upon connecting our data.

Custom settings

Upon choosing one of the default dropdown options, you can also click the ... button to set more specific formatting.

You can also add multiple formatting rules within the same step by clicking on the Add Format Rule button. Please note that your formatting rules will be applied from the top down.

Numbers

When you select to format columns as Numbers, you will have three additional settings to configure:

  1. Comma grouping: You can choose whether to apply comma groupings or not. Selecting to apply comma grouping will add a comma for every thousands.
  2. Decimals: You can input how many decimal places you'd like to display.
  3. Rounding: You can choose whether to round to the nearest value, round down, or round up.

Percents

When you select to format columns as Percents, you will have three additional settings to configure:

  1. Comma grouping: You can choose whether to apply comma groupings or not. Selecting to apply comma grouping will add a comma for every thousands.
  2. Decimals: You can input how many decimal places you'd like to display.
  3. Rounding: You can choose whether to round to the nearest value, round down, or round up.

Currency

When you select to format columns as Currency, you will have three additional settings to configure:

  1. Currency: You can select a desired currency among the following options: Dollars ($), Euros (€), Indian Rupees (₹), Pounds (£), and Yen (¥)
  2. Decimals: You can input how many decimal places you'd like to display.
  3. Rounding: You can choose whether to round to the nearest value, round down, or round up.

Accounting

When you select to format columns as Accounting, you will have three additional settings to configure:

  1. Currency: You can select a desired currency among the following options: Dollars ($), Euros (€), Indian Rupees (₹), Pounds (£), and Yen (¥)
  2. Decimals: You can input how many decimal places you'd like to display.
  3. Rounding: You can choose whether to round to the nearest value, round down, or round up.

The main difference between the Currency and Accounting options are that accounting will display negative numbers in parentheses ($5.00) while currency  displays negative numbers with a negative sign in front -$5.00.

Invert Number

The Invert number (+/-) option will switch the sign of the value, making all positive numbers negative and all negative numbers positive.

Helpful tips

  • The Format numbers step can also remove formatting from numbers. For example, if you have a column that contains the value, "$4000", you can configure to format this column as a "Number" which will update the formatting of the value to "4000".