Splitting a Column

It is easy to split a column containing text (a string column) into two or more columns, using the expanded data in analysis flyout in Spotfire. You can choose to split on any separating character, like space, comma, @, etc. You can also trim away unwanted characters from the original string at the same time.

Note: You must have some data loaded in the analysis and the Data in analysis flyout must be shown. The column to split must be a text-based (string) column.

To split a column:

1. On the authoring bar, click Data in analysis , and click on the column you want to split.

2. In the expanded flyout, make sure that Details on selected column is selected.

3. In the expanded flyout, locate the Actions section and click Split column.

4. In the Split Column dialog, verify that the suggested separator is correct. The characters used as separator will be removed from the resulting column values.

   Comment: You can use any character such as ".", " " (whitespace), "@", or a combination of different characters as the separator.

5. Verify that the suggested number of columns is correct. If you are only interested in the first (or last) values in the string to split, you can reduce the number of new columns here.

6. Optionally, you may want to remove unwanted characters from the beginning or end of the original string, before performing the split. For example, if you have string values within a parenthesis or within quotation marks, you can trim away these characters by specifying a number of characters to remove from the beginning or end of the string.

7. Optionally, if you are more interested in the values at the end of the string, you can click Split from end of string, to start counting the substrings to extract from the end instead of from the beginning.

   Comment: For example, this can be important if you want to extract information about different countries from a list of email addresses, where the number of different parts included in the addresses vary between different rows. See the Splitting email addresses example below.

8. Click OK.

   Response: The new columns show up in the data in analysis flyout. The new columns are added as calculated columns, and it is possible to edit their name or expression in the same way as for other calculated columns. For a description of the Split function, see Text functions.

Splitting email addresses

If you want to split a list of email addresses on the form "firstname.lastname@company.country", it might be difficult for Spotfire to guess what you want to do with your splitting operation, and you may need to do some manual configurations.

If it is the names you are interested in, you might want to start by doing a split using "@" as separator, to get the names in one column, and then do a subsequent split on the new name column using "." as separator, to separate the first name from the last name.

However, if all you are interested in is the country code, you can instead choose to split on "." directly, set Number of new columns to "1", and select Split from end of string, to get the country codes only in a new column.