Working with Dollar Formats and Dollar Signs in Alteryx

Working with dollar signs in Alteryx can range from changing numbers to currency format to removing dollar signs from a column to convert a string column to a numeric one. We’ll explain the different methods for dollar formatting and removing currency symbols.

Dollar and currency formatting can be applied in Alteryx using Formula Tools, Multi-Field formulas or the Table Reporting Tool.

While currency formatting in Alteryx isn’t as easy as pushing a button like you can in Excel, it’s still fairly quick. We’ll explain how it’s a multi-step process and how you can quickly convert numbers to currency format, add comma separators and round values with a single formula.

Let’s roll!

Converting Numbers to Currency Format in Alteryx

Unfortunately, there is no currency formatting button in Alteryx. Instead, there is a two step process. First, change the number to a string and add a thousand seperator comma using the ToString formula. Second, concatenate a dollar sign to the beginning of the number value.

Alteryx interprets any non numeric character in a field as text, which includes commas and dollar signs. Because of this, numbers must also be converted to strings (text) as part of the process to add dollar symbols and commas.

It’s important to keep this in mind because any calculations you need to do to a column of numbers must be completed prior to updating the final formatting and converting the column to a string data type.

Adding Commas and Rounding Numbers with a ToString Formula

To add commas and round values in Alteryx, add a Formula tool from the Prepartion section of the Alteryx Toolbar. Then use the ToString Formula using the syntax: ToString([Column Name],2,1) where 2 represents the number of decimal places for rounding, and 1 turns on the thousands separator.

The screenshot below shows a new column being created to convert the Sales (numeric) column to a Sales (String) column that includes a comma and rounds to 2 decimal places using a ToString formula.

Adding a comma separator and rounding using the ToString formula in Alteryx

You cannot change the data type of a column inside of a Formula tool, which means you need to create a new column with a String data type instead of updating a column in place.

If you don’t want to create a new column, you can apply formulas to multiple columns at one time and update the data type in a single step using the Multi-Field formula in the Preparation section of the Alteryx Toolbar.

Concatenating Dollar Symbols to Numbers

To add a dollar sign to a number in Alteryx, use a Formula tool to concatenate a dollar sign to the beginning of a number. The syntax is as follows: “$” + [Column Name]. Text must be surrounded by quotes and the + sign is used to tell Alteryx to concatenate.

For this method to work, the target column must be assigned to a String (text) data type. Because the $ symbol is considered a text character you will not be able to directly connect it to a numeric field.

Concatenating a dollar symbol to the start of a ToString formula to create Alteryx currency formatting

It’s worth noting that you can temporarily convert a number to a string field using a ToString formula even outside the context of adding commas and rounding. The temporary conversion can be combined with a ToNumber formula within a Formula tool to combine multiple steps into one.

For more control over the rounding process, you can round seperatly from the ToString formula, check out our Guide to Rounding Numbers in Alteryx.

Removing Dollar Symbols from Strings in Alteryx

To remove a dollar symbol from a String, add a Formula tool to your workflow from the Preparation section of the Alteryx toolbar. Select an existing column or add a new column, and use the Replace formula with the following syntax: Replace([Column Name],”$”,””)

The replace formula targets a column, then looks for the dollar symbol surrounded by quotes “$” and instead of defining a replacement character we apply two quotes with nothing in between “” which replaces the $ symbol with nothing, effectively removing it from the field.

Screenshot of setting up a Replace formula in Alteryx to remove dollar signs from a column

If you have many different columns to remove dollar symbols from, you can use the Multi-Field Formula tool to apply the same formula to many different columns of a data set in a single step.

Adding Dollar Symbols with the Table Reporting Tool

To add a dollar sign when using the Alteryx Reporting tools, select the Basic table tool and insert the prefix as $. Then select the number of decimal places to round to. Preview the Table Tool output with a connected browse tool. Comma separators must be added with a formula prior to data entering the Table Tool.

The Reporting Tools in Alteryx allow you to create paginated reports that can be output to formats such as .PDF Files or to add coloring of alternating rows to a table prior to outputting data to an Excel workbook.

Because the report tools render the incoming data, most changes need to made prior to the data entering the tools.

The example below shows a Basic Table tool configured to add a $ symbol as a prefix and rounding the number of decimal places.

Setting up a Table Tool in Alteryx to add a Dollar Prefix and decimal places for currency formatting

You’ll notice in the image above that the workflow results say “Table – view Browse Tool Report Tab”. This is because the data is rendered as an image and can only be viewed with a connected browse tool.

Tip: To quickly add a browse tool to any other tool in Alteryx press CTRL + SHIFT + B on the keyboard with a tool or multiple tools selected.

Use a Browse tool to view a newly rendered reporting tool output

Make sure that all data transformations and calculations are complete prior to placing data into the reporting tools. Also be aware that you must output using a Render Tool from the Reporting section once data enters a Reporting Tool. The regular Output tool will no longer work.

Conclusion

Adding and removing dollar signs in Alteryx is quick and easy. To add a dollar sign, use a Formula tool and the ToString formula to first convert a number to a string, add a comma separator for thousands, and round the value. Then concatenate a dollar sign onto the beginning of the newly formatted number.

You can convert multiple columns into currency format using the similar method and the Multi-Field Tool. You can also pre-pend a dollar sign to a Reporting Table, set the number of decimal places and output a rendered report using the reporting tools. However, this approach does not round values and will not render thousand separators. They must be calculated in a formula prior to entering the reporting tools.

Scroll to Top