Welcome Guest.


Formula error in excel, say let’s type before the first character

Asked by: Jennifer Cook 229 views IT February 11, 2019

=IF(COUNTIF(C3,"*JJ*"),"CJZX",IF(COUNTIF(C3,"*P*"),"PFTZ",IF(COUNTIF(C3, "*2018004*"),"PFTY",IF (COUNTIF(C3,"*J2019013*"),"HJZY",IF(COUNTIF(C3,"*2019013*"),"HJYX",IF (COUNTIF(C3,"*H*"),"HPWT",IF (COUNTIF(C3,"*2019012*"),"HPHH",IF(COUNTIF(C3, "*20180100*"),"HYSM","")))))))

4 Answers

  1. +8Votes  

    Select Yes:


    =IF(COUNTIF( C3,"*JJ*"),"CJZX",IF(COUNTIF(C3,"*P*"),"PFTZ",IF(COUNTIF(C3,"*2018004*") , "PFTY", IF (COUNTIF(C3,"*J2019013*"),"HJZY",IF(COUNTIF(C3,"*2019013*"),"HJYX",IF(COUNTIF( C3,"*H*"),"HPWT",IF (COUNTIF(C3,"*2019012*"),"HPHH",IF (COUNTIF(C3,"*20180100*") ,"HYSM",""))))))))))

    If the calculation result of the formula is not what you want, then continue to modify it.

    You didn’t explain your calculation rules, so it’s not good to modify your formula.

    Answer: After the formula is analyzed:
    , IF (COUNTIF (C3, “*P*”), “PFTZ”
    , IF (COUNTIF (C3, “*2018004*”), “PFTY”
    , IF (COUNTIF (C3, “*J2019013*”), “HJZY”
    , IF (COUNTIF (C3, “*2019013*”), “HJYX”
    , IF (COUNTIF (C3, “*H*”), “HPWT”
    , IF (COUNTIF (C3, “*2019012*”), “HPHH”
    , IF (COUNTIF (C3, “*20180100*”), “HYSM”, “”)))))))))
    It is not case sensitive. If you want to distinguish between upper and lower case, you can modify it to:
    IFERROR (IF (FIND (“2019012”, C3), “HPHH”),

    Answer: The effect of the two formulas is as follows:

    Ann Lewis- February 12, 2019 |

  2. +6Votes  

    After copying your formula to the cell, press OK to automatically correct the error and get the correct result as follows:

    =IF(COUNTIF(C3, "*JJ*"),"CJZX",IF(COUNTIF(C3,"*P*"),"PFTZ",IF(COUNTIF(C3,"*2018004*"),"PFTY",IF(COUNTIF(C3,"*J2019013*"),"HJZY",IF(COUNTIF(C3,"*2019013*"),"HJYX",IF(COUNTIF(C3, "*H*"),"HPWT",IF (COUNTIF(C3,"*2019012*"),"HPHH",IF (COUNTIF(C3,"*20180100*"),"HYSM","")))))))))

    Peter- February 12, 2019 |

  3. +2Votes  

    It seems that there is nothing wrong with the formula, except that there is one parenthesis missing and I don’t know why it is outside the condition plus *

    Joshua Harrison- February 13, 2019 |

  4. +7Votes  

    (1) Click “No”——“OK”, the cursor will automatically go to the error, and the confirmation is here and then corrected here.

    (2) Click “Yes” to correct it automatically. (Note: AutoCorrect is not necessarily correct)

    Sandra White- February 13, 2019 |