How to Concatenate and Combine Columns in Alteryx

Columns can be merged together in Alteryx using a formula to concatenate the contents of each one. When using this method you can either create a new column of the combined values or replace the values in an existing column. There are also some creative ways to automatically combined values from multiple rows that will concatenate a data set with specified delimiters.

Alteryx users can concatenate values with a formula tool or a summarize tool depending on the use case

Concatenating values within Alteryx is simple to accomplish with the use of a Formula tool. Other advanced methods can be used to combine multiple rows into a single value with a Summarize tool. However, the most important pre-requisite is making sure that the data you wish to concatenate starts with a text or string data type.

Let’s dive in!

Data Types for Concatenating in Alteryx

Alteryx supports a number of different data types that define what actions can be performed on a column or multiple columns of data. Data types are less important in software like Microsoft Excel they are crucially important in Alteryx. They determine what actions can be applied to columns in formulas and mis-matches can cause errors.

For example, to concatenate two columns of values each column must be assigned a string (aka: text) data type. If you attempt to concatenate two columns of numbers, the data will be added together. If you try to add a numeric column and a string column the results will be an error.

You can learn more about the different data types available in Alteryx with their help guide explaining each data type.

Concatenate Two or More Columns with a Formula Tool

To combine values from two or more columns, use a Formula tool located in the Preparation section of the Alteryx Toolbar. Connect it to your data, and either select an existing column to update or create a new column. Enter the name of the column surrounded by square brackets and combine with another value or column using the + symbol.

Here’s how in more detail.

First, add a formula tool and connect it to incoming data. In our example we are going to combine a First Name and Last Name to create a full name with a space in-between the words

The formula tool is available in the preparation section of the Alteryx toolbar

Once it’s connected there will be a configuration panel on the left side of the screen for the formula tool. You can use the dropdown menu to either “Select Column” from an existing column or Add a Column.

A new blank formula tool added onto an Alteryx canvas

When you click on the “Select Column” dropdown box, a menu appears to select an existing column or add a new one. If you choose to add a new one, the formula will result in a column to the far right of your data set based on the formula you enter. Choosing an existing column will replace the values in that column with the formula entered into the formula box.

Adding a new column with an Alteryx formula to combine first and last name fields

The Syntax for concatenating fields in Alteryx uses the following rules.

  • [Square Brackets] surround field names.
  • Text must be defined by surrounding it with double quotes.
  • Concatenation is performed with the + symbol.
  • Only text values can be concatenated. Numeric values are added. Mis-matches cause errors.

In this example, we define both of our column names and concatenate them with a + symbol along with a space character in-between two quotes.

Example of a formula being used to concatenate first and last name fields with a space in-between

As you can see from the results, when we run the workflow, a new Full Name column is generated by concatenating values from the First Name and Last Name Columns.

Even though we only combined values from two columns, you can use the formula tool to combine as many values as you need to. There is no column limit in Alteryx.

Fixing Data Type Mismatches when Concatenating in Alteryx

A “Parse Error at char (): Type mismatch in operator +.” error message in Alteryx means that there is a mis-match between a numeric and string data type when attempting to concatenate the fields. To correct the error, add a Select tool prior to the formula and change the concatenated columns to String data types.

Here’s what the error type will look like when you try to concatenate strings with numeric data.

A data type mismatch when trying to concatenate

To fix the error, add a Select tool from the Preparation section to change the data type of a column before it goes into the formula tool.

In the example below, we have an address number that’s being read as a numeric interger. Click on INT64 or the other numeric data type and adjust it to match a V_String or V_WString data type.

Viewing a data type with a select tool to change it before a concatenate formula

Another way to identify the error prior to running a workflow is that the formula box will not automatically colorize text when the formula is invalid. The example below shows the first part of concatenating an address number with the street name turned black meaning that it is an invalid formula.

Example of a concatenate formula showing you that it is not a valid formula in Alteryx

Be somewhat cautions in Alteryx when relying on the coloring to tell you when a formula is accurate or not, there are a number of tools that do not use coloring at all, such as the multi-row and multi-field tools.

Using the ToString Formula in Alteryx

To avoid data type errors you can either update a field data type using a select tool prior to data reaching the formula tool, or you can temporarily change the data type directly in a formula tool. To change the way that a specific formula evaluates a field, use the ToString, ToNumber, or ToDate formulas.

These formulas are especially useful when you only need to change a data type once for the purposes of a formula and do not want to make the change permanent throughout the workflow.

Example of using a ToString formula in an Alteryx formula to temporarily convert a number to a string

The Syntax for ToString is: ToString([Column Name]) and there are additional options as well that you can learn more about from the Alteryx Data Conversions Help Guide.

Using the Summarize Tool to Concatenate Values

You can use the Summarize tool in Alteryx to concatenate values in rows into a single value. Add a Summarize tool from the Transpose section of the Alteryx Toolbar. Then configure the tool to apply the “Concatenate” action to a column and define a delimiter.

In our example, we will use the Full Name column of our data set and consolidate it into a single long string where each name is separated by a comma. This technique is often used when setting up files to output into a specific file upload format.

Adding a summarize tool to an Alteryx workflow to concatenate rows

On the configuration panel of the Summarize tool, Add a column that you want to combine by clicking on it at the top, and choosing “Add” in the middle of the panel. The values that are listed at the bottom of the configuration panel can be configured with different aggregation methods depending on their data type.

Configuring an Alteryx summarize tool to concatenate

The concatenate function in the Summarize tool is located under, String > Concatenate.

Changing a Summary tool's group by settings to String > Concatenate

The results of the Summarize tool in this case will be one long string of text where all full names, one for each row, have been consolidated and separated by commas.

The end result of concatenating many rows of data using a summarize tool and a comma Seperator in Alteryx

If you’re working with more complex data that either has a risk of containing typos or if there are commas inside the data itself, you can define start and end delimiters such as double quotes or you can use special characters for a separator instead of a comma, such as a pipe | to avoid errors when delimiting the file in the future.

Conclusion

To concatenate columns in Alteryx, you can use a formula tool. Define the column by surrounding the column name in square brackets and use a plus sign + to combine the two columns of text. It’s common to add a space between concatenated columns which can be defined as a space surrounded by double quotes.

When concatenating values from rows to turn into one long string, you can use a Summarize tool configured to concatenate with start, end and middle Seperators that can be defined as commas or custom values.

Scroll to Top