yogi_Given 'Never' Is 0 'Occasionally' is 1 'Often' is 2 'Very Often' is 3 Enumerate Text Entries In Column E

Google Spreadsheet   Post  #2038

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-17-2016

post by: Sub Help:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/R3Q0cGO2Y54;context-place=mydiscussions
Substituting Numbers for Text Using Arrary Formula

I am attempting to substitute numbers for text. However, two text response include the same word ("Often" and "Very Often"). I would like Sheets to substitute a "2" for "Often" and a "3" for "Very Often". 

I composed the following formula:

=ArrayFormula(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!AZ2:AZ10 , "Never" , 0) , "Occasionally" , 1) , "Often" , 2) , "Very Often", 3))

The formuala properly substitutes numbers for text with one exception. Instead of substituting a "3" for "Very Often", the output is "Very 2". 

Please help!

--------------------------------------------------------------------------------------------------------------------