MID error when using two calculated columns for integer parameters

[JustinNgan]

I would like to use the MID function for a new column. MID (Text, Start Position, Number of Characters)

Note, both Position and Number of Characters are calculated columns.

When I check this, I receive the error "An argumen of function 'MID' has the wrong data type or has an invalid value."

When I replace one of the calculated numbers with a test number, it works.

Why does it appear as though I can only use one calculated column for the parameters to my MID function?

[JustinNgan · Accepted solution]

Whole numbers 😞  but I think you got it ... there are definitely some records where the value of the calculated columns are null/blank.  I guess I can use the function to calculate on only those with valid entries by wrapping an outer function to test for a number?

[JustinNgan]

One additional point, as I continue to try and figure this out, it appears that it is using a Column as the LENGTH parameter that is causing the error with the mid FUNCTION.

Can anyone else try to use a MID function on their data and see if this is indeed a case / problem?

[Trotzuk]

I just tried using calculated columns as both the start position and the length and it worked.

What are the data types of the columns you are using? Do you have sample data? Are the columns you are using for the start position and length ever null, blank, or text?

[JustinNgan · Accepted solution]

Whole numbers 😞  but I think you got it ... there are definitely some records where the value of the calculated columns are null/blank.  I guess I can use the function to calculate on only those with valid entries by wrapping an outer function to test for a number?

[Trotzuk · Accepted solution]

I just confirmed that a blank being returned in a calculated column value will cause the error you posted. You can get around that by doing something like this:

USERNAME = MID([LOG_MESSAGE], if(isblank([USERNAME_START]), 1, [USERNAME_START]), if(isblank([USERNAME_LENGTH]), 1, [USERNAME_LENGTH]))

Not knowing what your data looks like, I just used the isblank to check to see if there is a blank value in the calculated column and then replaced the blank with a 1. Same with the length in the second parameter. You may have to pick other values, but that should prevent blanks from breaking your formula.

[JustinNgan]

THANK YOU TROTZUK, 

Some extra help finding the cause of errors from smart folks like yourselves makes the experience much better!  

Again, thanks for your help!