Forum Discussion

mchl_hrms's avatar
mchl_hrms
Copper Contributor
Nov 14, 2023

Turn text within a cell into a value (number)

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?

excel
  • djclements's avatar
    djclements
    Bronze Contributor
    Nov 14, 2023

    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.

Resources