This post will guide you how to limit cell entries to specific length and number of digits in Excel. How do I limit data entry to allow only text or only numbers entered into in a cell using Data Validation option in Excel. How to limit entries to numeric values in Excel.
- Limit Data Entry to Allow Only Text
- Limit Data Entry to Allow Only Numeric Values
- Limit Maximum Cell Value
- video: Limit Data Entry
Assuming that you want to limit data entry for a range of cells in your worksheet, such as: only allow to enter text values or numeric values in the selected range of cells. How to do it. You can refer to the following introduction.
Limit Data Entry to Allow Only Text
If you want to only allow entering Text values in the selected range of cells, you can use the Data Validation feature to achieve it. Just do the following steps:
#1 select the range of cells that you want to limit data entry.
#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.
#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISTEXT function in Formula Text box. And click Ok button.
=ISTEXT(A1)
This formula will check if the cell value is a text value or not, if TRUE, it returns TRUE, otherwise, returns FALSE, and the Data Validation rules will be triggered.
Note: A1 is the first cell in your selected range.
Limit Data Entry to Allow Only Numeric Values
If you want to only allow entering numeric values in the selected range of cells, you can use the Data validation feature in combination with the ISNUMBER function to achieve the result. Do the following steps:
#1 select the range of cells that you want to limit data entry.
#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.
#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISNUMBER function in Formula Text box. And click Ok button.
=ISNUMBER(A1)
The ISNUMBER function returns TRUE when cell value is a number and returns FALSE if not.
Note: A1 is the first cell in your selected range.
Limit Maximum Cell Value
If you want to limit data entries are not greater than or euqal to a given value (200) in a range, just do the following steps:
#1 Select the range of cells that you want to limit maximum cell value.
#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.
#3 click Settings tab in the opening Data Validation dialog, select Whole number in the allow drop down list box. And select less or equal to from the Data drop down list box. And then enter a numeric value 200 into text box of Maximum. Click OK button.
Related Functions
- Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)… - Excel ISTEXT function
The Excel ISTEXT function used to check if a value is text. If so, returns TRUE; if the text is not text, the function will return FALSE.The syntax of the ISTEXT function is as below:=ISTEXT(value)…
