Example of recoding using a conversion table

A company has offices in eight states. A manager needs to recode state names to state abbreviations in multiple worksheets. The manager creates a conversion table of state names and abbreviations and then copies the conversion table to the worksheets where the conversion is needed.

  1. Follow these steps to create the conversion table:
    1. Copy the following data:
      State Abbreviation
      California CA
      Colorado CO
      Montana MT
      Nebraska NE
      Ohio OH
      Oregon OR
      Pennsylvania PA
      Washington WA
      Note

      The first value in each column is the column name.

    2. Click in the first column name cell and paste the data. You can also type the data in the worksheet.
  2. Follow these steps to enter the data values to recode.
    1. Copy the following data:
      Location
      Ohio
      California
      Oregon
      Pennsylvania
      Pennsylvania
      Washington
      Ohio
      Montana
      Nebraska
      Montana
      Note

      The first value in the column is the column name.

    2. Click in the next open column name cell and paste the data. You can also type the data in the worksheet.
  3. Choose Data > Recode > Use Conversion Table.
  4. In Recode values in the following column, enter Location.
  5. In Current values, enter State.
  6. In Recoded values, enter Abbreviation.
  7. Click OK.

Results

The recoded values are in a new column at the end of the worksheet.

C1-T C2-T C3-T C4-T
State Abbreviation Location Recoded Location
California CA Ohio OH
Colorado CO California CA
Montana MT Oregon OR
Nebraska NE Pennsylvania PA
Ohio OH Pennsylvania PA
Oregon OR Washington WA
Pennsylvania PA Ohio OH
Washington WA Montana MT
    Nebraska NE
    Montana MT
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy