Results 1 to 9 of 9

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

Threaded View

  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

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