Create a formula that takes the 4th symbol of a cell, If the 4th symbol is 8 the result from the for-CodePudding

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!!!

CodePudding user response：

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.