'MID' has the wrong data type or has an invalid value

[kivanct]

Hi,

It is a very long formula but I would be grateful if you could help me. I am receiving " 'MID' has the wrong data type or has an invalid value" error. How can i fix that ?

Kivanc =

VAR IndexOfFirstSpace = FIND(" ", ZP38[Malzeme kısa metni], 1, LEN(ZP38[Malzeme kısa metni]))

VAR IndexOfSecondSpace =

FIND(

    " ",

    ZP38[Malzeme kısa metni],

    FIND(

        " ",

        ZP38[Malzeme kısa metni],

        1,

        LEN(ZP38[Malzeme kısa metni])

    ) + 1,

    LEN(ZP38[Malzeme kısa metni])

)

VAR IndexOfHyphen = FIND("-", ZP38[Malzeme kısa metni], 1, LEN(ZP38[Malzeme kısa metni]))

VAR IndexOfUnderscore = FIND("_", ZP38[Malzeme kısa metni], 1, LEN(ZP38[Malzeme kısa metni]))

VAR IndexOfParanthesis = FIND("(", ZP38[Malzeme kısa metni], 1, LEN(ZP38[Malzeme kısa metni]))

VAR IndexOfSlash = FIND("/", ZP38[Malzeme kısa metni], 1, LEN(ZP38[Malzeme kısa metni]))

VAR FirstMinIndex = IF(IndexOfFirstSpace < IndexOfHyphen, IndexOfFirstSpace, IndexOfHyphen)

VAR SecondMinIndex = IF(IndexOfUnderscore < IndexOfParanthesis, IndexOfUnderscore, IndexOfParanthesis)

VAR MinIndexOfTwo = IF(FirstMinIndex < SecondMinIndex, FirstMinIndex, SecondMinIndex)

VAR MinIndexOfAll = IF(MinIndexOfTwo < IndexOfSlash, MinIndexOfTwo, IndexOfSlash)

VAR _1stMDASY = MID(ZP38[Malzeme kısa metni], 8, MinIndexOfAll - 8)

VAR _2ndMDASY = IF(MID(_1stMDASY, 5, 1) = "R", MID(_1stMDASY, 1, 4), _1stMDASY) 

VAR _3rdMDASY = IF(MID(_2ndMDASY, 6, 1) = "R", MID(_2ndMDASY, 1, 5), _2ndMDASY)

VAR _4thMDASY = IF(MID(_3rdMDASY, 7, 1) = "R", MID(_3rdMDASY, 1, 6), _3rdMDASY)

VAR _5thMDASY = IF(MID(_4thMDASY, 8, 1) = "R", MID(_4thMDASY, 1, 7), _4thMDASY) 

VAR _6thMDASY = IF(ISERROR(IF(NOT(ISNUMBER(VALUE(MID(_5thMDASY, 6, 1)))), MID(_5thMDASY, 1, 5), _5thMDASY)),_5thMDASY, IF(NOT(ISNUMBER(VALUE(MID(_5thMDASY, 6, 1)))), MID(_5thMDASY, 1, 5), _5thMDASY)) 

VAR _7thMDASY = IF(ISERROR(IF(NOT(ISNUMBER(VALUE(MID(_6thMDASY, 8, 1)))), MID(_6thMDASY, 1, 7), _6thMDASY)),_6thMDASY, IF(NOT(ISNUMBER(VALUE(MID(_6thMDASY, 8, 1)))), MID(_6thMDASY, 1, 7), _6thMDASY))  

VAR _8thMDASY = IF(LEFT(_7thMDASY, 2) = "20", MID(_7thMDASY, 3, LEN(_7thMDASY) - 2), _7thMDASY) 

VAR _9thMDASY = IF(RIGHT(_8thMDASY, 1) = "S", MID(_8thMDASY, 1, LEN(_8thMDASY) - 1), _8thMDASY) 

VAR _10thMDASY = IF(LEN(_9thMDASY) = 7 && MID(_9thMDASY, 6, 1) = "A", MID(_9thMDASY, 1, LEN(_9thMDASY) - 2), _9thMDASY) 

VAR _11thMDASY = IF(LEN(_10thMDASY) > 7, MID(ZP38[Malzeme kısa metni], 10, 4), _10thMDASY)

RETURN

IF(

    LEFT(ZP38[Malzeme kısa metni], 10) = "CONT.BOARD",

    TRIM(

        MID(

            ZP38[Malzeme kısa metni],

            IndexOfFirstSpace + 3,

            IndexOfSecondSpace - IndexOfFirstSpace - 2 

        )  

    ),

    TRIM(_11thMDASY)

)

[v-jingzhang]

Hi @kivanct 

You have used MID in the 11 variables _xxxxxMDASY and any variable will use the previous variable's result. If one variable cannot return the correct result, it will affect the following variables. You can return the results of variables one by one from the first variable to find out from which one it returns this error. Then correct the formula from then on. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[jdbuchanan71]

@kivanct 

My guess is that the 2nd part of your MID formula "IndexOfSecondSpace - IndexOfFirstSpace - 2" is returning a negative of a blank in some cases.  For testing, try making 3 mesures.

1. that returns  IndexOfFirstSpace
2. that returns  IndexOfSecondSpace
3. that retuturns  IndexOfSecondSpace - IndexOfFirstSpace - 2

and see what the result of those values is.