1. Home
  2. Recoding Column Values

Recoding Column Values

Recoding a column lets you list each unique value in a factor, variate or text column and change it to a new value. The new group values are then added back to the spreadsheet as a new column, which can be either text or numeric.

You can use this to change multiple occurrences of a single value to a new one (for example, changing all zeros to missing values or vice-versa), or for recoding some text entries on to a numerical scale (e.g. ‘Bad’, ‘Poor’, ‘Average’, ‘Good’ to 1,2,3,4 or ‘No’, ‘Yes’ to 0,1). You can also use the recode feature to group levels of a factor together, or define sets of values for factor groups.

  1. Put the cursor in the spreadsheet column you want to recode then select Spread | Factor | Recode.
    OR
    Spread | Calculate | Recode.
  2. In the New Values column, type your new values.
    You can combine two groups together by giving them the same value.

  3. Enter a name for the new column and specify whether it will be a factor, numeric or text (the checkbox options shown depend on what type of column you have selected).
  4. Click OK to create the new column.

Old values Lists all the unique values or text found in a column.
New values Lets you type in new values or text for each corresponding old value.
Counts Shows the frequency of occurrence of each unique value found in the selected column. This list is provided for information only and cannot be changed.
Recoded column name Specifies a name for the new column.
Create as a factor The resulting column will be a factor.
Recode to numeric The resulting column will be a variate.
Recode to text (Only shown if the selected column is a variate) New values will be displayed as text, and the resulting column will be a text.
Use date format (Only shown if the selected column is a date) New values will be displayed as dates.
Reset Resets the dialog, undoing any changes you have made.
Ordinals (Only shown if the selected column is a factor) Click to toggle between the different display formats for a factor.
Fill Opens the Numerical Sequence for Recode dialog (see below) that lets you automatically generate a pattern or sequence of numbers to fill the recoded column.
Numerical sequence for recode

You can generate a sequence of numbers for the recoded column by providing a starting and ending value along with an incremental value. Numbers can also be repeated within the sequence using a pre-multiplier.

  1. Click the Fill button on the bottom right of the Recode Column dialog.
  2. Enter values into each field then click OK.

Starting value Specifies the first value to be used in the sequence.
Ending value

Specifies the last value to use in the sequence. If the ending value is not in the progression of numbers specified by the starting value and increment then the pattern will terminate at the largest incremental value less than the ending value.

For a sequence of decreasing numbers the pattern will terminate at the smallest incremental value greater than the ending value.

Increment Specifies the increment to be used to form the intermediate values in the sequence. If the starting value is greater than the ending value then a negative increment must be specified.
Number of repeats Allows a pre-multiplier to be applied to the sequence, so that each element is repeated the specified number of times.
Preview Provides an illustration of the sequence of numbers that will be inserted within the column. Note that for very long sequences only the first 20 and last 20 items are displayed, separated by the string ‘…’.

Updated on May 17, 2019

Was this article helpful?