+ Reply to Thread
Results 1 to 9 of 9

Data Validation: 7 or 8 digits only, preserve leading zeros

  1. #1
    Registered User
    Join Date
    06-26-2018
    Location
    Searcy, AR
    MS-Off Ver
    MAC 2011
    Posts
    3

    Data Validation: 7 or 8 digits only, preserve leading zeros

    Since I had to leave the cell format as text to preserve any leading or trailing zeros entered by the user, I had a formula that worked for 8-digits only:

    =AND(LEN(A1)=8,COUNT(-MID(A1,ROW(INDIRECT("1:8")),1))=8)

    But now I found out the number length is sometimes also 7 digits. I tried creating an OR function, but flat out cannot get it to work. It may be something as simple as too few or too many parentheses?

    =OR((LEN(K4)=8,COUNT(-MID(K4,ROW(INDIRECT("1:8")),1))=8)),(LEN(K4)=7,COUNT(-MID(K4,ROW(INDIRECT("1:7")),1))=7)

    Or maybe I'm missing something else basic, but I've looked until my eyes are crossed. Can anyone help?

    Attached file is an example.
    Attached Files Attached Files
    Last edited by Feefee32; 06-26-2018 at 12:15 PM. Reason: upload example file

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Hello and welcome to the forum.

    You will likely get a quicker, more accurate response by sharing a small representative sample of your data along with the desired result (which you can enter manually) based on the sample data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    06-26-2018
    Location
    Searcy, AR
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Thanks for the tip. File uploaded.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Try

    =AND(OR(LEN(A1)=7,LEN(A1)=8),ISNUMBER(--A1))
    Last edited by jason.b75; 06-26-2018 at 12:22 PM.

  5. #5
    Registered User
    Join Date
    06-26-2018
    Location
    Searcy, AR
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Yay! So much simpler and better. thanks for the quick help!

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Even simpler now, I changed the method that I was using after I started typing so it had an extra AND() that wasn't needed. Just edited it out.

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Just for the fun of it...

    The formula in post #4 can be shortened to this:

    =AND(OR(LEN(A1)={7,8}),ISNUMBER(--A1))

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Didn't think you could use array constants in data validation rules.

    =OR(LEN(A1)=7,LEN(A1)=8)/A1

    As long as there is at least 1 digit >0
    Last edited by jason.b75; 06-26-2018 at 01:00 PM. Reason: corrected misplaced parenthesis

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Data Validation: 7 or 8 digits only, preserve leading zeros

    Whoops, you can't.

    I didn't know where the formula was going.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 08-11-2015, 12:29 PM
  2. Data Validation - Leading Zeros
    By RogueArchon in forum Excel General
    Replies: 7
    Last Post: 05-23-2014, 01:19 PM
  3. Preserve formatting Leading Zeros in CSV
    By rolta100 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2014, 07:00 AM
  4. Preserve leading zeros in a varying length number when converting it to text
    By Paulymon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2013, 12:48 AM
  5. [SOLVED] CSV data has leading zeros - how can I open in Excel to preserve them?
    By Hang Glider in forum Excel General
    Replies: 4
    Last Post: 01-29-2013, 07:47 PM
  6. Replies: 1
    Last Post: 09-28-2012, 03:40 PM
  7. Preserve leading zeros
    By BCRose in forum Excel General
    Replies: 3
    Last Post: 10-18-2009, 04:34 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1