Text to Columns

TextToColumns.SK is a sheetkraft function used to separate the contents of excel cell into separate columns, provided that the text data is-

• • separated with delimiter or

  • we have a fixed number of characters to retrieve or

  • we need to retrieve pattern of characters from a given data.

TextToColumns.SK function can be accessed in excel add-in, in the SheetKraft tab-

SheetKraft > More > Text To Columns, as shown in the following figure:

On clicking Text To Columns, a UI is displayed:

Text Range : The input data must be selected here.

(Note: we can use RegionDown.SK of the cell, if similar format dataset is present in vertical cells.)

File Type :

· If the data is in delimiter separated format, use the Delimited File Type. TextToColumns.SK function provides all the major delimiters such as comma, pipe, space, etc.

· If we have a fixed number of characters to retrieve, use Fixed Width File Type.

· If we need to retrieve pattern of characters from a given data, use Use Regex File Type.

The working of each File Type is elaborated below:

(i) Delimited

(ii) Fixed Width

(iii) Use Regex

(i) Delimited :

We can use, Delimited file type when the input data describes delimiters such as comma, pipe, space, etc.

Delimited File Type UI has following arguments-

· The Dropdown beside the Delimited radio button contains the delimiter types, select the delimiter as per requirement.

· Import specific columns checkbox must be checked.

· A preview appears, which displays the result data which will be generated. Number of columns to be formed is mentioned as Column 1, Column2,… ,tick the checkbox beside it to generate required columns.

· Format: It is the format of the given data, it can be text, number, date or general.

· If Blank: There is a possibility that the data which will generate in the column might be blank, so in this case we can provide value.(Default is blank value).

· If Error: There is a possibility that the data which will generate in the column might have error, so in this case we can provide value.(Default is #VALUE! value).

(i) Fixed Width :

We can use, Fixed Width File Type when we have a fixed number of characters to retrieve from the input data.

Fixed Width File Type UI has following arguments-

· Width (number datatype) to be entered in the textbox beside the Fixed Width radio button.

· Import specific columns checkbox must be checked.

· Format: It is the format of the given data, it can be text, number, date or general.

· If Blank: There is a possibility that the data which will generate in the column might be blank, so in this case we can provide value.(Default is blank value).

· If Error: There is a possibility that the data which will generate in the column might have error, so in this case we can provide value.(Default is #VALUE! value).

(i) Use Regex :

We can use, Use Regex File Type when we need to retrieve pattern of characters from a given data.

Use Regex File Type UI has following arguments-

· When Use Regex File Type is used, we see a table having two columns, the regex is generated based on the characters selected in the left column.

· Select the text to be retrieved, and press the combination key, the selected text will be highlighted.

· Then click on the Generate button, the text in right column will automatically get highlighted, if both the columns text matches, click on Run button and then on OK button to execute the function.

· After this next UI will appear which is same as previous File Type UI.