Wednesday, February 17, 2016

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   Feb-17-2016
post by: Sub Help:!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!