How to use data validation to restrict character length in Excel cells?
If you want users to enter a limited number of characters in an Excel cell (for example, only 7-10 characters), how can you do this? This article explains how to limit the number of digits using the Data Validation function in Excel.
Use Data Validation function to limit number of digits in Excel cells
Use Data Validation function to limit number of digits in Excel cells
If you want users to enter only 7-digit or 10-digit strings in cells, follow these steps:
1. Select the cells you want to limit digits, click Data > Data Validations > Data Validation.
2. Under Settings tab, select Custom from Allow drop-down list, then type this formula =OR(LEN(A1)=7,LEN(A1)=10) into Formula textbox.
3. Click OK. Now, the selected cells will only allow 7-digit or 10-digit strings. Otherwise, a dialog box will pop up to warn the user.
Note: If you just want users to type number strings, use this formula =OR(AND(LEN(A1)=7,LEN(A1)=10),ISNUMBER(A1)).
If you want users to enter only 7- to 10-digit strings in cells, follow these steps:
1. Select the cells you want to limit digits, click Data > Data Validations > Data Validation.
2. Under the Settings tab, select Text length from Allow drop-down list, choose between in Data drop-down list, in Minimum and Maximum textbox, type the number you use.
3. Click OK. Now the selected cells only can type strings in digits between 7 and 10.
Tip: If you want to display a specific warning to remind users, follow these steps:
In the Data Validation dialog, under Error Alert tab, type the reminder in the Error message textbox.
Then click OK, while users enter the invalidation data, the message pops out to remind users type right data.
| Tabbed Browsing & Editing for Multiple Word Documents/Excel Workbooks, Just Like in Chrome and Edge! |
Just like browsing multiple webpages in Chrome, Safari and Edge, Office Tab lets you open and manage multiple Word documents or Excel workbooks in one Word window or Excel window. Switching between documents or workbooks is now simple with a click on their tabs! |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in