Skip to content
geeksforgeeks
  • Courses
    • DSA to Development
    • Get IBM Certification
    • Newly Launched!
      • Master Django Framework
      • Become AWS Certified
    • For Working Professionals
      • Interview 101: DSA & System Design
      • Data Science Training Program
      • JAVA Backend Development (Live)
      • DevOps Engineering (LIVE)
      • Data Structures & Algorithms in Python
    • For Students
      • Placement Preparation Course
      • Data Science (Live)
      • Data Structure & Algorithm-Self Paced (C++/JAVA)
      • Master Competitive Programming (Live)
      • Full Stack Development with React & Node JS (Live)
    • Full Stack Development
    • Data Science Program
    • All Courses
  • Tutorials
    • Data Structures & Algorithms
    • ML & Data Science
    • Interview Corner
    • Programming Languages
    • Web Development
    • CS Subjects
    • DevOps And Linux
    • School Learning
  • Practice
    • Build your AI Agent
    • GfG 160
    • Problem of the Day
    • Practice Coding Problems
    • GfG SDE Sheet
  • Contests
    • Accenture Hackathon (Ending Soon!)
    • GfG Weekly [Rated Contest]
    • Job-A-Thon Hiring Challenge
    • All Contests and Events
  • Excel Tutorial
  • Excel Formulas
  • Excel Shortcut Keys
  • Data Analysis in Excel
  • Formatting in Excel
  • Excel Workbooks
  • Statistical Functions
  • Data Visualization in Excel
  • Pivot Tables in Excel
  • MS Excel Quiz
  • Excel Interview Questions
  • Advance Excel
Open In App
Next Article:
How to Format Numbers in Thousands and Millions in Excel?
Next article icon

Use Custom Data Validation Function To Limit Number of Digits In Excel

Last Updated : 29 Oct, 2021
Comments
Improve
Suggest changes
Like Article
Like
Report

There might arise a situation while working on Excel where you want the users to type in 10-digits(say)  numbers only in a cell. In these types of scenarios, we can make use of the Custom Data validation Function to limit the number of digits in Excel. In this article, we will discuss the same.

Sample Data: Customer Sales Table

Sales Table

Approach:

First, we check the given cell value is number and then the cell value is only 10 digit. Allow only both conditions are true

Excel Formula:

  • ISNUMBER(): Return Boolean value True if the cell value is number.
  • LEN(): Return the number of characters in a cell.
  • AND(): Logical and function.

Implementation of Custom Data Validation Function:

Follow the below steps to implement the custom data validation function:

Step 1: Type Header (“Mobile Number” as an example) in Cell C1.

Step 2: Select cells “C2” to “C21”

Step 3: Click Data >> Data Validation >> Data Validation… to open the Data Validation dialog box as shown below. Here we will be implementing the function as :

AND(LEN(C2)=10,ISNUMBER(C2))

Error Alert:

Data validation popup an Error Alert, when the condition is not met.


Next Article
How to Format Numbers in Thousands and Millions in Excel?

S
sk123online
Improve
Article Tags :
  • Excel
  • News
  • Microsoft Office
  • Excel-VBA
  • ExcelGuide

Similar Reads

  • How to Validate Number of Function Arguments in MATLAB?
    MATLAB functions are generally defined input and output arguments. To validate the number of these input-output arguments, MATLAB provides us with the following functions: narginchknargoutchkSyntax:narginchk(<minimum-inputs>, <maximum-inputs>) nargoutchk(<minimum-outputs>, <maxi
    3 min read
  • How to calculate Sum of Digits in a Number using Non Array Formulas in Excel?
    In Excel, we can easily add the digits of a number. Usually, calculations that involve multiple values can be dealt with the help of array formulas, but here, we are going to find the sum of the digits of a number using non-array formulas. As an example, say we have a number 238 in one of the cells
    4 min read
  • How to Create a User Defined Function in Excel VBA
    A function is a collection of code.  As a developer, we very often need a custom function (User-defined function) in our projects. These functions can be used as normal functions in Excel. These are helpful when the existing functions are not enough. In such cases, the user can create his own custom
    6 min read
  • How to Validate Input to a Function Error in R
    In this article, we will examine various methods for how to validate input to the function by using R Programming Language. How to validate input to the function?Validating input to a function in R is crucial for ensuring that your code behaves as expected and can handle various types of input grace
    5 min read
  • How to Format Numbers in Thousands and Millions in Excel?
    Microsoft Excel allows its users to store raw data in a well-structured format in a spreadsheet. The spreadsheets consist of numerous cells in the form of rows and columns in which an individual can store data. An Excel spreadsheet can store different types of data such as numbers, characters, strin
    4 min read
  • Function Argument Validation in MATLAB
    The basics of Function Argument is, " The functions receive certain inputs from the calling command which applies in function syntax and processes it. sometimes It may or may not produce output argument it dependence completely on the functions efficiency or the accuracy of your code." Function Argu
    5 min read
  • how to use validate_comma_separated_integer_list in django
    A validator is a callable that takes a value and raises a ValidationError if it doesn’t meet the criteria. Validators can be useful for re-using validation logic between different types of fields. In this article, we will learn how to use 'validate_comma_separated_integer_list' validator in Django.
    4 min read
  • How to find Sum the Digits of a given Number in PHP ?
    We will explore how to find the sum of the digits of a given number in PHP. This task involves extracting each digit from the number and adding them together to get the final sum. Table of Content Using a loop to extract digits one by oneUsing mathematical operations to extract digitsUsing a loop to
    2 min read
  • Custom Format to Show Units Without Changing Number to Text in Excel
    Microsoft Excel is a great software that is used by individuals to store data in the form of spreadsheets in a formatted manner. Microsoft Excel allows us to store a variety of data such as characters, text, numbers, decimals, date, time, etc. Since Excel is a smart tool, it automatically identifies
    3 min read
  • How To Convert Date To Number String Or Text Format In Excel?
    Excel's built-in date system by default supports dates between January 1, 1900, and December 31, 9999. In this range, the numbers start at 1 and increase by 1 each time. In addition to text and numeric data types, Excel also allows dates that are internally stored as serial numbers. In Excel, the da
    4 min read
geeksforgeeks-footer-logo
Corporate & Communications Address:
A-143, 7th Floor, Sovereign Corporate Tower, Sector- 136, Noida, Uttar Pradesh (201305)
Registered Address:
K 061, Tower K, Gulshan Vivante Apartment, Sector 137, Noida, Gautam Buddh Nagar, Uttar Pradesh, 201305
GFG App on Play Store GFG App on App Store
Advertise with us
  • Company
  • About Us
  • Legal
  • Privacy Policy
  • In Media
  • Contact Us
  • Advertise with us
  • GFG Corporate Solution
  • Placement Training Program
  • Languages
  • Python
  • Java
  • C++
  • PHP
  • GoLang
  • SQL
  • R Language
  • Android Tutorial
  • Tutorials Archive
  • DSA
  • Data Structures
  • Algorithms
  • DSA for Beginners
  • Basic DSA Problems
  • DSA Roadmap
  • Top 100 DSA Interview Problems
  • DSA Roadmap by Sandeep Jain
  • All Cheat Sheets
  • Data Science & ML
  • Data Science With Python
  • Data Science For Beginner
  • Machine Learning
  • ML Maths
  • Data Visualisation
  • Pandas
  • NumPy
  • NLP
  • Deep Learning
  • Web Technologies
  • HTML
  • CSS
  • JavaScript
  • TypeScript
  • ReactJS
  • NextJS
  • Bootstrap
  • Web Design
  • Python Tutorial
  • Python Programming Examples
  • Python Projects
  • Python Tkinter
  • Python Web Scraping
  • OpenCV Tutorial
  • Python Interview Question
  • Django
  • Computer Science
  • Operating Systems
  • Computer Network
  • Database Management System
  • Software Engineering
  • Digital Logic Design
  • Engineering Maths
  • Software Development
  • Software Testing
  • DevOps
  • Git
  • Linux
  • AWS
  • Docker
  • Kubernetes
  • Azure
  • GCP
  • DevOps Roadmap
  • System Design
  • High Level Design
  • Low Level Design
  • UML Diagrams
  • Interview Guide
  • Design Patterns
  • OOAD
  • System Design Bootcamp
  • Interview Questions
  • Inteview Preparation
  • Competitive Programming
  • Top DS or Algo for CP
  • Company-Wise Recruitment Process
  • Company-Wise Preparation
  • Aptitude Preparation
  • Puzzles
  • School Subjects
  • Mathematics
  • Physics
  • Chemistry
  • Biology
  • Social Science
  • English Grammar
  • Commerce
  • World GK
  • GeeksforGeeks Videos
  • DSA
  • Python
  • Java
  • C++
  • Web Development
  • Data Science
  • CS Subjects
@GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox
Improvement
Suggest Changes
Help us improve. Share your suggestions to enhance the article. Contribute your expertise and make a difference in the GeeksforGeeks portal.
geeksforgeeks-suggest-icon
Create Improvement
Enhance the article with your expertise. Contribute to the GeeksforGeeks community and help create better learning resources for all.
geeksforgeeks-improvement-icon
Suggest Changes
min 4 words, max Words Limit:1000

Thank You!

Your suggestions are valuable to us.

What kind of Experience do you want to share?

Interview Experiences
Admission Experiences
Career Journeys
Work Experiences
Campus Experiences
Competitive Exam Experiences