1. Home
  2. Converting a Column Type

Converting a Column Type

You can easily convert columns from one data type to another. For example, if you have imported a numeric column as text, you can convert it to a factor or variate.

  1. First select one or more columns to convert.
    • To select several non-adjacent columns hold down Ctrl and click each column header.
    • To select several adjacent columns hold down Shift then click the first and last column header in your selection.
    • To convert columns to factors without sorting them by level select Spread | Factor | Convert. No further action is required.
    • To convert columns to variates or texts, or to convert to a factor and sort the levels as well, select Spread | Column | Convert.
      The dialog that appears will depend on whether you have a single column or multiple columns selected.
  3. Set options as required then click OK.
This dialog opens when a single column is selected This dialog opens when multiple columns are selected
Column type Specifies the new type for the selected columns.

  • Variate columns are generated using the values that are currently displayed in the column. Any factor labels or text are converted to numbers wherever possible, otherwise they are replaced by missing values.
  • Factor columns are generated from variates by forming a set of levels from the distinct set of values in the column. Similarly, factor labels are formed from the distinct set of values in a text column.
  • Text columns are generated by converting the values currently displayed into strings.
  • Units vector columns (for vector spreadsheets only) are columns displayed next to the row numbers and provide additional identification for the rows of a spreadsheet. For example, the Tag column in the spreadsheet below records the identifying number on the tag worn by each sheep in the dataset. A Units vector is formed using the currently displayed text or numeric values.
Text options These options control how strings are grouped together. When a text is converted to a factor, the strings in the text will be truncated to 60 characters (maximum factor label length).

  • Ignore case – Selected columns are converted ignoring case sensitivity. For example, ‘male’, ‘Male’ and ‘MALE’ would be set as one level if this was selected and three separate levels otherwise.
  • Ignore blanks – Leading and trailing blanks are removed so that strings which are identical apart from leading or trailing blanks will be grouped together.
Sort levels
(factors only)
Sorts the factors within each of the selected columns by their levels.
Tolerance on creating factor levels

A positive value in this field will be used as a tolerance in creating levels from a factor. If two values differ by less than this tolerance, they will be assigned to the same factor group. A value of 0 puts all unique values into their own group. If the values have been calculated, then values that display as the same may differ in their final few significant figures.

For example, the calculated values 1/3 and 200/600 may be assigned to different groups depending on how the final figure rounds. A small value such as 1e-12 could then be used to allocate these two distinct values to the same group.

The default value for this item is taken from the option Tolerance on creating levels from variate in the Tools | Spreadsheet Options Conversions tab.

Read text as date values  When converting a text to a variate you can use this option to read the text as a date (e.g. 31/12/98). When you select this option the Date format button becomes enabled so that you can specify the date format. The base date (day 1) is also set via the Date format button.
Updated on June 10, 2019

Was this article helpful?