Search code examples
excelif-statementexcel-formulaformulahelper

Create a formula that takes the 4th symbol of a cell, If the 4th symbol is 8 the result from the formula needs to be 2018

Please help me with the formula for this:

Create a formula that takes the 4th symbol of column A (Product_ID). If the 4th symbol is 8 the result from the formula needs to be 2018, if the symbol is 9 the result needs to be 2019

Product ID: IP1966712 IP1867546

I would really appreciate your help!

Thank you!!!

Solution

  • If the format of the number is always the same then to get the 4th character you can use:

    =value(mid(A1,4,1))

    Then you can use if():

    =if(value(mid(A1,4,1))=8,2018,if(value(mid(A1,4,1))=9,2019))

    You may need to trap errors.

    Then, based on the answer provided by JvdV, you can also do this:

    =IF(--MID(B10,4,1)=8,2018,IF(--MID(B10,4,1)=9,2019))

    Where the double negative "--" deals with either True/False or numbers that are taken by excel as text as they are from text functions.