How do I assign a number value to a non-numerical value in Excel

I have some survey responses with values like "VU" for "Very Unlikely" and "S" for Sometimes. Each survey response occupies a cell. For each cell containing a survey response, I want to fill another cell with a corresponding number. For example, for every cell containing "VU" I want to fill a corresponding cell with the number 1.

How is this done?

I would have a seperate table in another tab that details the values:

Code   Value
VU     1
U      2
S      3
L      4
VL     5

Then, in a new column, on the survey I would add this formula:

=VLOOKUP(A2,CodeSheet!$A$2:$B$6,2,0)

where A2 refers to the respondents answer for that row and Codesheet refers to the sheet where you put the above value table.

Now you should be able to sort by the new column.

You can easily do this with a formula. Something like:

=IF(A1="VU",1,IF(A1="S",2,IF(A1="L",3,IF(A1="VL",4,""))))

Where A1 is the cell containing the response.

I used Very Unlikely, Sometimes, Likely, Very Likely as possible responses.

It would also be possible to create a VBA macro to do this, but i think that would be complicating it...