+ Reply to Thread
Results 1 to 12 of 12

How to check digits of a number

  1. 10-22-2020, 10:52 AM #1
    kakero
    kakero is offline
    Registered User
    Join Date
    04-28-2020
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    27

    How to check digits of a number

    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
    Attached Images Attached Images
    Last edited by kakero; 10-22-2020 at 12:35 PM.
    Reply With QuoteRegister To Reply
  2. 10-22-2020, 10:53 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: How to check digits of a number

    Is it always 3 digits?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 10-22-2020, 10:55 AM #3
    kakero
    kakero is offline
    Registered User
    Join Date
    04-28-2020
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    27

    Re: How to check digits of a number

    Hi. 1 to 8 digits . not always 3
    Reply With QuoteRegister To Reply
  4. 10-22-2020, 12:21 PM #4
    mikeava
    mikeava is offline
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: How to check digits of a number

    All in different cells?
    Reply With QuoteRegister To Reply
  5. 10-22-2020, 12:28 PM #5
    kakero
    kakero is offline
    Registered User
    Join Date
    04-28-2020
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    27

    Re: How to check digits of a number

    No - All digits will be in only ONE CELL
    Reply With QuoteRegister To Reply
  6. 10-23-2020, 07:03 PM #6
    mikeava
    mikeava is offline
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: How to check digits of a number

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-23-2020, 07:42 PM #7
    mikeava
    mikeava is offline
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: How to check digits of a number

    Forgot to check for 2 digits.
    See updated file. Please let me know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 10-23-2020, 09:48 PM #8
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,657

    Re: How to check digits of a number

    With number in A1

    This returns TRUE/FALSE, use it for conditional formatting

    Please Login or Register  to view this content.
    Array formula, confirmed with Ctrl-shift-enter
    Quang PT
    Reply With QuoteRegister To Reply
  9. 10-23-2020, 11:44 PM #9
    GeoffW283
    GeoffW283 is offline
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: How to check digits of a number

    Another way:

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

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu
    Reply With QuoteRegister To Reply
  10. 10-25-2020, 03:46 AM #10
    kakero
    kakero is offline
    Registered User
    Join Date
    04-28-2020
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    27

    Re: How to check digits of a number

    Thank you very much
    Reply With QuoteRegister To Reply
  11. 10-25-2020, 03:47 AM #11
    kakero
    kakero is offline
    Registered User
    Join Date
    04-28-2020
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    27

    Re: How to check digits of a number

    Quote Originally Posted by GeoffW283 View Post
    Another way:

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

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Thank you very much
    Reply With QuoteRegister To Reply
  12. 10-25-2020, 04:28 AM #12
    hrlngrv
    hrlngrv is offline
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: How to check digits of a number

    Quote Originally Posted by bebo021999 View Post
    . . .
    Please Login or Register  to view this content.
    Would return an error when A1 has just 1 numeral, in which case INDIRECT("1:"&0) would be the cause.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count number of unique X digits duplicate pairs from a number
    By SedoSan7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2019, 04:03 AM
  2. How to ID digits within a group of 8-digits that equal a specified number
    By Thunder001 in forum Excel Formulas & Functions
    Replies: 31
    Last Post: 07-04-2019, 02:49 AM
  3. [SOLVED] VBA Filter Out Unique Digits Permutation via Check Box Mark
    By AndyJr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2018, 12:47 AM
  4. Check most frequent number in 1 column that has 4 digits number
    By Merson78 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-07-2014, 10:57 PM
  5. Sum digits in a cell plus the number digits-Ready for this
    By mccoy54 in forum Excel General
    Replies: 5
    Last Post: 12-08-2008, 11:47 AM
  6. Check digits between column
    By iamrony in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2008, 11:24 PM
  7. ISBN Check Digits
    By Colin Vicary in forum Excel General
    Replies: 3
    Last Post: 08-12-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1