Splitting Rows and Columns

Version

Application Scenario

When you edit data, you can click Split Field to split strings according to your needs.

Function Description

You can split by the separator or by the character quantity.

Split by the separator: USA-California-Sacramento can be split into three field values: USA, California, and Sacramento using - as the separator.

Split by the character number: MYY001 has a total of six characters. It can be split into two field values with three characters: MYY and 001.

1. Choose More > Split Field on the data editing page to go to the function page.

2. The function page is shown in the following figure.

Settings	Options	Description
Select Field	The text type field of the current table.	Select the field to split.
Split Method	By Separator	Example: USA-California-Sacramento can be split into USA, California, and Sacramento using - as the separator.
	By Character Number	Example: MYY001 has a total of six characters. It can be split into two field values with three characters: MYY and 001.
Split to	Column	Split one field value into multiple columns.
	Row Note: 1. This function does not support splitting by character number. 2. This function is not supported in the direct connection.	Split one field value into multiple rows.
Split Result	Number N Column	Select one field to keep.
	Top N Column	Select the top N fields to keep.

You can download the example: Spilt Rows and Columns.xlsx.

Split Through the Separator

Note:

Oracle does not support the split_part function. Therefore, Oracle tables do not support splitting columns by the separator in the directly connected mode.

1. To extract the states from the Targeted State string, split the states by using : as the separator, select the Number N Column for Split Result, and enter 1 on the right side.

2. To preserve the states and cities after splitting Targeted State, select Top N Column for Split Result, and enter 2 on the right side. Both fields can be kept and displayed after splitting.

Delete the original field and name the latter split field City through Field Settings.

3. To split City into columns with only one city in each column, split the cities by ,, select Top N Column for Split Result, and enter 4 on the right side because the field with the most values contains four cities.

However, you may feel that splitting into columns is inconvenient for further analysis and calculations. It would be better if the field could be split into rows.

4. Split City into rows.

Select City for Select Field, By Separator for Split Method, and , as the separator.

Split Through the Character Quantity

Note:

Splitting by the character quantity does not support splitting to rows.

The Staff ID was a string, which made it inconvenient to sort and compare by value. In this case, you can extract the numeric part of Staff ID.

1. Select By Character Quantity for Split Method, with a character number of 3.

2. Modify the field of the numeric part to Value for more convenient comparison and sorting.