1. Home
  2. Combining Text to Create new Columns (concatenate)

Combining Text to Create new Columns (concatenate)

You can join text strings together, such as factor labels, by combining text from up to 8 columns into a new text column. The columns that are combined can be of any type (text, variate or a factor column displaying text labels) and the currently displayed text in the column will be used. This allows you to combine text from numerical columns where the displayed numbers are treated as a text rather than then an actual number.

  1. From the menu select Spread | Calculate | Combine Text.

     

  2. Select the Number of columns to combine, then select the required columns from the dropdown lists. (You can select the same column more than once.)
  3. Enter a name for the new column in Save into text column.
  4. To put a separator, such as a space, between the combined texts, select Insert separator between texts. Deselect this option if no separator is required.
  5. Set other options as required then click OK to combine the texts into a new column and close the dialog, or click Apply to keep the dialog open for another combine operation.

Options

Start

Specifies the position of the first character to transfer to the new column. For example, if this option is set to 3, then the string ‘ABCDE‘ will transfer the characters ‘CDE‘ to the combined text column.
Width Allows you to extract a subset of characters from the one column to transfer to the new column. Width specifies the maximum number of characters that can be transferred. For example, if the Start position is 2, and the Width is 3, then the string ‘ABCDE‘ will transfer the characters ‘BCD‘ to the combined text column.
Trim spaces before combining columns Removes any leading or trailing spaces from each column entry before combining it into the new column.
Save into Text Column

Lets you enter a new name or select an existing column name.

Note: if you select an existing name this column will be replaced with the new column i.e. you will overwrite the existing data.

Insert separator between texts

Inserts a separator between each string from the combined columns.

  • Space – inserts a space between strings e.g. combining strings ‘AB‘ and ‘CD‘ will create ‘AB CD‘.
  • Comma – inserts a comma between strings e.g. combining strings ‘AB‘ and ‘CD‘ will create ‘AB,CD‘.
  • Other – lets you specify one or more characters to insert between strings e.g. if you type a space, -, and another space, then combining strings ‘AB‘ and ‘CD‘ will create ‘AB – CD‘.
Remove double separators Cells with missing values will be excluded from the combined text to prevent repeated separators. For example, if the 3 items ‘A’, <empty cell> , and ‘C’ were combined with a separator of ‘+’, then the result would be ‘A++C’. If Remove double separators is selected the separator ‘+’ for the missing cell value will be excluded resulting in the string ‘A+C’.
Apply Use the specified criterion to combine the text from the columns into a new column and leave the dialog open for another combine operation.
Clear Clear any fields that have been filled in.

Updated on March 1, 2019

Was this article helpful?