Forum Discussion

kivanct's avatar
kivanct
Icon for Helper I rankHelper I
3 years ago

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

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)
)
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    3 years ago

    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.

     

     

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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!