Turn text within a cell into a value (number)

Copper Contributor

I am trying to assign a number value to specific text strings.  Example - "Strongly agree" = 5, "Agree" = 4, "Neutral" = =3, and so on.  I tried to use the IFS function with no success.  Any suggestions?

1 Reply

@mchl_hrms If the value you are trying to convert is in cell A1, for example, the syntax for the IFS functions would be as follows:


=IFS(A1="Strongly Agree", 5, A1="Agree", 4, A1="Neutral", 3, A1="Disagree", 2, A1="Strongly Disagree", 1, TRUE, 0)


However, the SWITCH function would be easier to use in this case, as the expression to evaluate only needs to be entered once. For example:


=SWITCH(A1, "Strongly Agree", 5, "Agree", 4, "Neutral", 3, "Disagree", 2, "Strongly Disagree", 1, 0)


Using 0 as the final argument will return 0 if no match is found, or if cell A1 is blank.


Note: the IFS and SWITCH functions are both available in Excel for MS365, Excel 2021 and Excel 2019.