Limiting number to certain amount of digits

I deal with SKUs that are 7 digits long. the first 6 digits represent the Master SKU and the 7th digit represents a variation of the Master SKU. In my table, sometimes the SKU comes in as 6 digits and sometimes at 7. I would like to create an expression that will delete the last digit of the SKU, ONLY if the SKU is 7 digits long so that way all SKUs in the column are always 6 digits. How would I go about this?

LEFT( [SKU] , 6 ) should work.

Hey @Marc_Dillon, thank you but didn’t seem to work. I should also mention that the SKU column is Ref type which I images is the reason it didn’t work

What was the result if it didn’t work?
Where did you enter the expression?
What is your data structure?
Does the referenced table have different key and label columns?

I put it in as an app formula and the result was that the SKUs remained the same number of digits, either 6 or 7. The referenced table has the same Key column (SKU). The label columns are different from each other whoever and neither are the SKU. The table which I am trying to apply the formula deals with our image database. The SKU metadata for the images are either 6 or 7 digits long. The master SKU table which the image table always has 6 digit SKUS.

Are you trying to input the expression in the SKU column? That won’t work. You’ll have to create a new column to hold the modified value.


Okay got ya. Just made a virtual column with the formula and turned that into the ref and it worked. I’m wondering why a virtual ref type column works with the formula but a non virtual ref type column wouldn’t?

It has nothing to do with real vs virtual.

You can’t modify the value in a column via specifying an app formula, especially one that references itself.
Presumably the SKU column is input by the user. Specifying an app formula is for creation of a value, and doing so prohibits user-entered data in that field.

1 Like

Okay, this makes sense. Thank you for the help @Marc_Dillon!

1 Like

Just an idea… you can change digits with an Event action when the form is saved. Then you can run the formula LEFT([SKU],6) with that action.

1 Like

This makes sense, but the SKUs aren’t provided by user input, they’re generated by an SQL query that overwrites the pre existing sheet on a daily basis.

1 Like

Okay… then it won’t work in your case.