I hope you can help.

I am currently designing a spreadsheet which will help us assess our
compliance with certain NHS standards.

Each standard has a cell. Each cell to the right of the standard has a pull
down list, where users can select 'Full Compliance', 'Partial Compliance' or
'Non-compliance' with regards to how well we are doing against each standard.

I would like each of these text responses to have a numerical value (e.g.
Full Compliance =2, Partial Compliance=1, Non-compliance=0) and I would like
this numerical value to automatically appear in the cell to the right of the
text response. Is this possible? I have tried the SUBSTITUTE function, but
this will only allow me to give one of the text responses a numberical value.
When I try and add details for the other two, errors occur.

Am quite new to using Excel, so any help would be much appreciated. Thanks