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.
Before you begin
You must have some
data loaded in the analysis and the analysis must be in
Editing mode. The column to split must be a
text-based (string) column.
Procedure
-
On the
authoring bar, click
Data in analysis
and click on the column of
interest.
-
In the expanded flyout,
make sure that
Details on selected column
is selected.
-
In the expanded flyout, locate the
Actions section and click
Split column.
-
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. You can use any character such as ".", " "
(whitespace), "@", or a combination of different characters as the separator.
-
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.
-
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.
-
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.
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.
-
Click
OK.
Results
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.