1. Home
  2. Combine/Concatenate Text Columns

Combine/Concatenate Text Columns

Select menu: Spread | Calculate | Combine Text

Use this to combine the text for up to 8 columns and concatenate into a new text column. The dialog also allows text to be used from numerical columns, where the displayed numbers are treated as a text rather than the actual number.

  1. From the menu select Spread | Calculate | Combine Text.
  2. Fill in the fields as required then click OK.

Number of columns to combine

This specifies the number of columns that are to be combined. You can select up to a maximum of 8 columns to be combined.

Columns to be combined

Provides a dropdown list of the current columns in the spreadsheet, where you can select the column to be combined. Note: a column can be selected more than once.

Start

This specifies the position of the first character in the column that is to be used when transferring text to the new column. For example, if this option is set to 3, then the string ‘ABCDE’ will transfer the substring ‘CDE’ to the combined text column.

Width

This specifies the maximum number of characters that can be transferred from the column to the combined text. For example, if the starting position is 2, and the width is 3, then the string ‘ABCDE’ will transfer the substring ‘BCD’ to the combined text column.

Save into text column

Specify the name of the column to store the combined text. A name can be entered or an existing column name within the current spreadsheet can be chosen from the dropdown list. Note that if an existing name is selected it will overwrite the data in that column.

Insert separator between texts

When selected, the specified separator will be inserted between the text from the combined columns.

Space Inserts a space between items, e.g. ‘A’ and ‘B’ combined would result in ‘A B’
Comma Inserts a comma between items, e.g. ‘A’ and ‘B’ combined would result in ‘A,B’
Other Inserts a specified string between the items, e.g. ‘A’ and ‘B’ combined with the specified string ‘ + ‘ would result in ‘A + B’

Remove double separators

When selected, any combinations of cells that include an empty cell will be excluded from the combined text preventing repeated separators. For example, if the items ‘A’,”, and ‘C’ were combined with a separator of ‘+’, then the result would be ‘A++C’. However, when this option is selected the separator ‘+’ for the missing cell will be excluded resulting in the string ‘A+C’.

Trim spaces before combining columns

When selected, leading, trailing and duplicate spaces will be removed from the text from each column before it is combined into the new column.

Action buttons

OK Use the specified criterion to combine the text from the columns into a new column and close the dialog.
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.
Cancel Close the dialog without making any changes.
Clear Clear any edit fields that have been filled in.

See also

The CONCATENATE procedure can be used within the command language to provide some of this functionality.

Updated on March 20, 2019

Was this article helpful?