How to Restrict the Lenght of Number Data Type

[TotalSolutions]

In India we have Pin Codes/Zip Code of 6 dights only. I want to restrict the entry to 6 digits only and the user should not be able to enter more or less than 6 digits and the data type is number not text. How do I do this?

I use LEN in text but it does not work in Number Data Type.

Please let me know. Thank you!!

[Suvrutt_Gurjar]

Please try
AND([_THIS]>=100000, [_THIS]<=999999)

[TotalSolutions]

Suvrutt_Gurjar:

AND([_THIS]>=100000, [_THIS]<=999999)

Superb!! Works perfectly!! Thank You!! God Bless!!

[TotalSolutions]

Also I have a column that can have numeric value of 4 to 6 digits. What will be the formula for the same?

Please let me know. Thank you!!

[Suvrutt_Gurjar]

Have you tried any expression? If so, could you share it.

In general, it will be very similar to the earlier expression.

[Steve]

[Koichi_Tsuji · Accepted solution]

Alternatives (if user can enter numbers such as 010101, starting from zero which is accepted as 6 digit numbers.

Then only solution should be set the data type to text, and ensure the each chara is numbers.

If we restrict 6 diget all the time simply

And(len([_THIS])=6,
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],1),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],2),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],3),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],4),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],5),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],6),1))
)

In case we accept 4 to 6 digit for single column then valid if should be something like

AND(
len([_THIS])>=4,
len([_THIS])<=6,
IFS(
len([_THIS])=4,
And(
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],1),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],2),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],3),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],4),1))
),
len([_THIS])=5,
And(
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],1),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],2),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],3),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],4),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],5),1))
),
len([_THIS])=6,
And(
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],1),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],2),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],3),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],4),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],5),1)),
Contains({0,1,2,3,4,5,6,7,8,9},Right(Left([_this],6),1))
)
)
)

i not fully tested this expression, but should be alright.

I just simply assumed his requirement something line PIN code where 0001 0010 0100 is there, while people claim they are 4 digit numbers.

[Koichi_Tsuji]

There should be more elegant (efficient) expresison though.

[TotalSolutions]

You are absolutely correct!! If the Pin Code/Zip Code starts with 0 we will have to go by your method!! as it does not calculate 0 in the lenght!!

I tried your code and it works!! You are so good at what you do!! You can write the code perfectly and don’t even need to test!! Thanks!! God Bless!!

One thing we can do is mention the Type Details Maximum Length: 6, Minimum Length: 6
and simply put the below in the Valid If. What is your opinion on that?

Contains ( { 0,1,2,3,4,5,6,7,8,9 },Right ( Left ( [_this], 1 ), 1 ) ),
Contains ( { 0,1,2,3,4,5,6,7,8,9 },Right ( Left ( [_this], 2 ), 1 ) ),
Contains ( { 0,1,2,3,4,5,6,7,8,9 },Right ( Left ( [_this], 3 ), 1 ) ),
Contains ( { 0,1,2,3,4,5,6,7,8,9 },Right ( Left ( [_this], 4 ), 1 ) ),
Contains ( { 0,1,2,3,4,5,6,7,8,9 },Right ( Left ( [_this], 5 ), 1 ) ),
Contains ( { 0,1,2,3,4,5,6,7,8,9 },Right ( Left ( [_this], 6 ), 1 ) )

[Steve]

[Suvrutt_Gurjar]

Total_Solutions:

In India we have Pin Codes/Zip Code of 6 dights only.

Hi @Total_Solutions ,

In India the 6 digit Pin Code never starts with 0 but it always starts with a minimum 100000 and can go upto 999999 . In short a valid Pin Code is always between 100000 and 999999.

There is no Pin Code starting with the first digit as 0.

So the simpler validation of AND([_THIS]>=100000, [_THIS]<=999999) should work or even the method suggested by @Steve should work.

As per my understanding, a simpler expression should do in this case.

[TotalSolutions]

That is also correct Pin Codes in India don’t start with a 0. So for Pin Codes we can use your simpler code and for other 6 digit and 4 digit stuff that could start with a 0 we can use the other code!!

Thanks to everyone for your kind help!