Split into columns

The Split into columns function is used to split data stored in a single line into columns . For example, data from CSV or log files.

Cells are filled with split data from left to right, starting from the selected cell or from the top cell of the selected column. Before splitting the data, make sure that there are enough empty cells to the right of the selected cell or column to accommodate the data.

The cells where the split data will be inserted should not contain the following:

–Pivot tables

–Parts of the array formulas

–Parts of merged cells

–Partially hidden merged cells

If the cells for insertion contain data, or if there are merged cells among them, or there are not enough cells for insertion, a dialog box with a corresponding notification is displayed. With the user's consent, existing data is overwritten with split data, merged cells are split, and data that goes beyond the sheet is considered lost.

When splitting data:

–No new columns are added, only the existing ones in the document are used.

–Hidden columns are not filled with data.

–If the data in a cell is preceded by an apostrophe ('), the apostrophe is ignored when filling in the first cell.

–Values that begin with the = sign are interpreted as formulas.

To split the data into columns, do the following:

1.Select the cell or column with the data that you want to split.

2.On the Data tab, click Split into columns.

3.In the Split into columns, specify the following parameters:

•Separators: Characters separating the values. Each value is then placed in a separate cell. For example, if you choose to use the semicolon (;) as the separator, the values in the string 1;1;1 will be placed into 3 separate cells. The separator can be selected from a list of fixed values or specified manually. To specify the separator manually, check the Custom box and enter the character in the field that appears. You can enter only one character in the field.
If necessary, you can select multiple separators in the list. For example, to place the words Street, City, Country from the data row Street;City%Country into separate columns, you should select the ; and % separators.

•Combine multiple separators into one: Consider separators that have no characters between them as a single separator. For example, if this checkbox is selected, the Country, City data will be divided into 2 cells: Country and City. If the checkbox is not checked, the data will be divided into 3 cells: Country, empty cell, City.

•Text qualifier: A character that encloses individual values. If the value is framed by qualifiers, it is placed in a separate cell even if it contains a separator. For example, if a comma is specified as a separator and double quotation marks are specified as a qualifier, the fractional number "3.14" is placed in a separate cell. If there is an unpaired qualifier in a string, then all data from the qualifier to the end of the string is placed in one cell.

•Once the window is closed, adjust column width to its contents: Automatically adjust the width of each column to the contents of the cell that contains the most data.

4.View the result of the data split in the Preview area. By default, the Preview area displays the first 50 rows selected for splitting.

5.Click OK.

You can use the following keyboard keys to move between elements in the Split into columns window:

–Tab: Go to the next group of elements or element.

–Shift + Tab: Go to the previous group of elements or element.

–↓, ↑, →, ←: Go to the separator located below, above, to the right, to the left.