Using Substring to Remove Last Digit on a 12 Character Field

[Fescobar]

Hello Everyone,

First post!

I am using the formula stated below to try and remove the last digit on a 12 character field.  I am also doing the same thing when the UPC is 13 characters long. 

I am trying to use this formula posted by Joe S from Alteryx.

Substring([Field1],0,length([Field1])-1)

When I do execute it I get this error as well.

Any assistance will be greatly appreciated. 

[marcusblackhill]

Hey @Fescobar !

I think is because your are replacing the word "length" by the string length but is to you keep the word because is the formula name.

hope that helps.

[marcusblackhill]

Try this:
Substring([Corp Item UPC],0,length([Corp Item UPC])-1)

[atcodedog05]

Hi @Fescobar 

I agree with @marcusblackhill 

You formula might go like

Substring([Corp Item UPC],0,12)

You can provide sample data and expected output. We can help you out 🙂

[Fescobar]

Thank you! 

This is an extremely small data sample.  

The Corp Item UPC Column (A) that needs either the 12th or 13th character removed.  The LEN Column (B) is a sanity check for me to get the correct numbers.

I also tried the formula below and I am seeing a different error.

Thank you!!

Fausto

[atcodedog05]

Ok

Adding to @marcusblackhill formula

Substring([Corp Item UPC],0,length(tostring([Corp Item UPC]))-1)

Currently [Corp Item UPC] is a number you can use Tostring () to convert it to string and use length which is a length function.

Hope this helps 🙂

[Fescobar]

Thank you both!

@marcusmontenegro and @atcodedog05

It doesn't seem to like that either unless I am doing something incorrectly which is likely 🙄.

[marcusblackhill]

Hey @Fescobar !

Now you have a data type problem, your trying to do a string function in a numeric field.replace the first [Corp Item UPC] by tostring([Corp Item UPC]), the rest you can keep the same.

[marcusblackhill]

Substring(tostring([Corp Item UPC]),0,length(tostring([Corp Item UPC]))-1)