How to check digits of a number

[kakero]

Hi

I want to define a rule for a data validation . I wan to force user to enter a number that its digit are in increasing order
first digit < second digit< third digit <.........

and also without any duplicate

length of number varies between 1 to 8 digit
All digit will be in ONE CELL



Any suggestion ?

Thank you

[Norie]

Is it always 3 digits?

[kakero]

Hi. 1 to 8 digits . not always 3

[mikeava]

All in different cells?

[kakero]

No - All digits will be in only ONE CELL

[mikeava]

OK - I finally figured it out. Since it wasn't certain how many digits the user was going to put in, made things interesting to say the least. So I had to figure separate formulas if it was 3 digits, or 4, etc. That is what is in Columns R - W.

Your result is in Column C (which points to R - W depending on the number of digits).

I copied the formula down to row 20. You can just keep copying down if you need more. If nothing is in the cell it will be blank.

Let me know if you have any questions.

[mikeava]

Forgot to check for 2 digits.
See updated file. Please let me know if you have any questions.

[kakero]

Thank you very much

[bebo021999]

With number in A1

This returns TRUE/FALSE, use it for conditional formatting

=AND(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1)-MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)>0)

Array formula, confirmed with Ctrl-shift-enter

[hrlngrv]

. . .

=AND(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1)-MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)>0)

Would return an error when A1 has just 1 numeral, in which case INDIRECT("1:"&0) would be the cause.

[GeoffW283]

Another way:

Number in A1, returns true or false. Regular non-array formula entry.

=SUM(--(MID(A1&"A"&REPT("A", 9-LEN(A1)), {1,2,3,4,5,6,7,8}, 1) < MID(A1&"A"&REPT("A", 9-LEN(A1)), {2,3,4,5,6,7,8,9}, 1))) =LEN(A1)

[kakero]

Another way:

Number in A1, returns true or false. Regular non-array formula entry.

=SUM(--(MID(A1&"A"&REPT("A", 9-LEN(A1)), {1,2,3,4,5,6,7,8}, 1) < MID(A1&"A"&REPT("A", 9-LEN(A1)), {2,3,4,5,6,7,8,9}, 1))) =LEN(A1)

Thank you very much