Split a Text Column

Select menu: Spread | Calculate | Text Split

To see a worked exampled refer to Splitting Values into new Columns (parsing)

This dialog allows strings within a text or factor column to be split into several new columns. The breaks in the text can be specified using specific characters (delimiters) or a specified location. The text items resulting from splitting the text up can be truncated to a specific length using the width specification for each result column or have extra spaces removed from it. The resulting columns can be automatically converted into factors.

After you have imported your data, from the menu select
Spread | Calculate | Text Split.
Fill in the fields as required then click OK.

One application of this menu is to split a factor with two part labels into two factors whose levels are specified by the first and second parts respectively. For example a factor with labels ‘N5’, ‘N10,’ ‘P5’, ‘P10’ could be converted into two factors with labels ‘N’, ‘P’ and ‘5’, ’10’ respectively.

Split column

A dropdown list of the current columns in the spreadsheet (text or factors) that can be split. Select the column that you want to split using this list.

Split using

These options specify how to split the text in each cell into the new columns. The options are:

Space delimiter	Split the text using spaces, e.g. ‘A B C’ would split to ‘A’, ‘B’, ‘C’
Comma delimiter	Split the text using commas, e.g. ‘A,B,C’ would split to ‘A’, ‘B’, ‘C’
Other delimiters	Split the text using delimiters specified in the Separator list, which will become visible when you select this option.
Specified positions	Split the text at specified positions, e.g. the string ‘ABCDE’ split at positions 1, 2 and 5 would result in ‘A’, ‘BCD’ and ‘E’. The positions should be specified in the Position list and the direction of counting is specified using the Positions counted from option below.
String delimiter	The complete string entered in Separator string is used to split the text. For example, if ‘to’ was entered as the separator string then ‘Medium to High’ would be split to ‘Medium’ and ‘High’ (with spaces trimmed as described below).

Position list

Specify a comma or space separated list of numbers to identify the positions where to split the text. For example, the list 1,2,4 would split the string ‘ABCDE’ into ‘A’,’BC’ and ‘DE’.

Separator list

This specifies a string of characters that specify where the breaks are to occur. For example, +-= would split ‘2+3=5’ to ‘2’, ‘3’ and ‘5’ (if the delimiters are not being retained as part of the split text).

Separator string

This specifies the String delimiter option, that specifies where the breaks are to occur. For example, ‘and’ would split ‘1 and 2 and 3’ to ‘1’, ‘2’. ‘3’.

Treat multiple delimiters as one

One or more delimiters (e.g. multiple spaces) are treated as a single break point in splitting the text. For example, if the delimiter is a space then the string ‘A B’ would be split into two items ‘A’ and ‘B’. However, if this item was not selected then, ‘A B’ would be split into three items ‘A’,’ ‘ and ‘B’ (the second split is a single space).

Keep delimiters

When selected, each delimiter will be retained in the split text. For example, if the string ‘A+B’ is split using the separator ‘+’, then this would be split into ‘A+’ and ‘B’. You can choose whether to include the separator on the left or right of the split text using the Delimiter stays on option.

Delimiter stays on

When the Keep delimiters option is selected, this specifies whether the delimiter is to be retained on the Left or Right of the split at that position. For example, if Left is selected and the delimiter is a ‘:’ then the string ‘A:B’ would be split into ‘A:’ and ‘B’. Alternatively, if Right is selected then the string ‘A:B’ would be split into ‘A’ and ‘:B’.

Positions counted from

When Specified start positions or Specified end positions is selected, this specifies whether the positions are counted from the Left or Right of the text. For example, in ‘ABCDE’, ‘A’ is in position 1 when counted from the left, and in position 5 when counted from the right.

Number of splits to save

This specifies the number of splits to save. If a text splits into more items than specified here, then the splits exceeding this number will be discarded. If fewer items are created, the columns after this will contain blank entries.

Column names

Lets you select or provide the name of a column to save the split text into. If a column already exists in the spreadsheet, then a warning will appear before any column is over written.

Width

Setting this item for each column specifies a maximum width of the resulting text items. Items longer than this will be truncated to this length.

Change text case in new columns

These options control how the case of the text in the new columns is changed:

Unchanged	The case of the text is unchanged from the original column.
Lower	The case of the text is changed to lower case (e.g. ‘ABC’ -> ‘abc’).
Upper	The case of the text is changed to upper case (e.g. ‘abc’ -> ‘ABC’).
Title	The case of the text is changed to title case, where the first letter in each word (i.e. the first character, and those following a space) are made upper case and all other lower case (e.g. ‘ABC’ -> ‘Abc’, ‘low rate’ -> ‘Low Rate’).

Trim spaces from split columns

Selecting this item removes leading, trailing and duplicate spaces from the resulting split up text.

Convert saved columns to factors

Selecting this option converts the resulting text columns into factors with labels.

Action buttons

OK	Use the specified criterion to split the columns into the new columns and close the dialog.
Apply	Use the specified criterion to split the text from the column and leave the dialog open for another split operation.
Cancel	Close the dialog without making any changes.
Clear	Clear any edit fields that have been filled in.