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","")))))))

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,”*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”, “”)))))))))
It is not case sensitive. If you want to distinguish between upper and lower case, you can modify it to:
=IFERROR(IF(FIND(“JJ”,C3),”CJZX”),
IFERROR(IF(FIND(“P”,C3),”PFTZ”),
IFERROR(IF(FIND(“2018004″,C3),”PFTY”),
IFERROR(IF(FIND(“J2019013″,C3),”HJZY”),
IFERROR(IF(FIND(“2019013″,C3),”HJYX”),
IFERROR(IF(FIND(“H”,C3),”HPWT”),
IFERROR (IF (FIND (“2019012”, C3), “HPHH”),
IFERROR(IF(FIND(“20180100″,C3),”HYSM”),””)))))))))

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

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","")))))))))

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 *

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)