Format numbers

‍

How to use the Format numbers step

1. Choose the number columns you want to format
   • Format these columns – Select specific columns to apply formatting to.
   • Format all columns except – Select specific columns to exclude from formatting.
2. Select a formatting option
   • Choose from the dropdown list.
   • Click the ellipses ... to customize options such as comma grouping, number of decimal places, and rounding.
   • Check the Invert number (+/-) box to switch positive numbers to negative and vice versa.
3. Add multiple formatting rules
   • Click Add Format Rule to create more than one rule within the same step.
   • Rules apply in top-down order.

Formatting options

Numbers

Use Numbers formatting to remove symbols and standardize values.
Example: $4000 becomes 4000.

Settings:

• Comma grouping – Choose whether to add commas every thousand.
• Decimals – Set the number of decimal places to display.
• Rounding – Round to the nearest value, round down, or round up.

Percents

Settings:

• Comma grouping – Add commas every thousand if needed.
• Decimals – Set the number of decimal places to display.
• Rounding – Round to the nearest value, round down, or round up.

Currency

Settings:

• Currency – Choose Dollars ($), Euros (€), Indian Rupees (₹), Pounds (£), or Yen (¥).
• Decimals – Set the number of decimal places to display.
• Rounding – Round to the nearest value, round down, or round up.

Accounting

Settings:

• Currency – Choose Dollars ($), Euros (€), Indian Rupees (₹), Pounds (£), or Yen (¥).
• Decimals – Set the number of decimal places to display.
• Rounding – Round to the nearest value, round down, or round up.

Key difference:

• Currency – Displays negative numbers with a minus sign (e.g., -5.00).
• Accounting – Displays negative numbers in parentheses (e.g., ($5.00)).

Special handling for accounting formats

If your data is already in accounting format, you must first remove the parentheses before applying new formatting. Parentheses cause the value to be read as a string, which can result in incorrect outputs (e.g., replacing with 0).

1. Add a Find/Replace step:
   • Rule 1: Find ( in any part of the cell → Replace with - in that part of the cell.
   • Rule 2: Find ) in any part of the cell → Replace with a blank.
2. After running these replacements:
   • ($7.00) becomes -$7.00.

To format numbers further, connect a Format Numbers step and apply the desired settings.