Limit not between number of characters in a cell
This tutorial shows how to limit entry of characters not between specified number of characters in a cell using Excel or VBA
Select a cell > Data tab > Data Tools group > Click on Data Validation > Data Validation > Select Settings tab > Select Text length > Select not between > Enter number for minimum and maximum > Click OK
| 1. Select a cell in which you want to limit entry for not between n number of characters. Note: in this example we are selecting cell B2. |
 |
| 2. Select the Data tab. |  |
| 3. Click on Data Validation in the Data Tools group. 4. Select Data Validation. |
 |
| 5. Select the Settings tab. 6. Select Text length in the Allow input box. 7. Select not between in the Data input box. 8. Enter the minimum number which represents the number of characters from which a user cannot enter in a selected cell. 9. Enter the maximum number which represents the number of characters up to which a user cannot enter in a selected cell. 10. Click OK Note: in this example we are applying a condition that will not allow you to enter between five and 10 characters in a selected cell. |
 |
METHOD 2. Limit not between number of characters in a cell using a formula
EXCEL
Select a cell > Data tab > Data Tools group > Click on Data Validation > Data Validation > Select Settings tab > Select Custom > Enter formula > Click OK
| 1. Select a cell in which you want to limit entry for not between n number of characters. Note: in this example we are selecting cell B2. |
|
| 2. Select the Data tab. | |
| 3. Click on Data Validation in the Data Tools group. 4. Select Data Validation. |
|
| 5. Select the Settings tab. 6. Select Custom in the Allow input box. 7. Enter the formula =OR(LEN(B2)<5,LEN(B2)>10) in the Formula input box. 8. Click OK Note: in this example we are applying a condition that will only allow you to enter less than five characters or greater than 10 characters in a selected cell. |
 |
METHOD 1. Limit not between number of characters in a cell
VBA
Sub Limit_not_between_number_of_characters()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B2")
Set Rng = ws.Range("B2")
'apply data validation
With Rng.Validation
With Rng.Validation
.Add Type:=xlValidateTextLength, Operator:=xlNotBetween, Formula1:="5", Formula2:="10"
End With
End Sub
ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet in which you want to apply a limit to a cell to restrict entry between specific number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Cell: Select the cell in which you want to apply a restriction between the number of characters that cannot be entered into it by changing the cell reference ("B2") in the VBA code.
Minimum Number of Characters: Select the minimum number which represents the number of characters from which a user cannot enter in a selected cell by changing the number "5" in the VBA code.
Maximum Number of Characters: Select the maximum number which represents the number of characters up to which a user cannot enter in a selected cell by changing the number "10" in the VBA code.
Worksheet Selection: Select the worksheet in which you want to apply a limit to a cell to restrict entry between specific number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Cell: Select the cell in which you want to apply a restriction between the number of characters that cannot be entered into it by changing the cell reference ("B2") in the VBA code.
Minimum Number of Characters: Select the minimum number which represents the number of characters from which a user cannot enter in a selected cell by changing the number "5" in the VBA code.
Maximum Number of Characters: Select the maximum number which represents the number of characters up to which a user cannot enter in a selected cell by changing the number "10" in the VBA code.
EXPLANATION
This tutorial shows how to limit entry of characters not between specified number of characters in a cell using Excel or VBA. Therefore, Excel will not allow you to enter the number of characters if they fall between the minimum and maximum specified number of characters. In this example we are only allowing users to enter less than five or greater than 10 characters in a selected cell.
This tutorial provides two Excel methods that can be applied to limit not between the number of characters that can be entered in a cell. The first method uses the Excel built-in data validation option (not between) and can be completed in 10 steps. The second method is achieved through the use of a formula in the Data Validation dialog box and can be completed in eight steps.
The VBA code in this tutorial uses a Validation function with the xlValidateTextLength validation type to limit not between n number of characters that can be entered in a cell.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Limit between number of characters in a cell | How to limit entry of characters between greater or less than number of characters in a cell | |
| Limit number of characters in a cell | How to allow to only enter n number of characters in a cell | |
| Limit maximum number of characters in a cell | How to limit users to only be able to enter up to a specific number of characters in a cell | |
| Limit minimum number of characters in a cell | How to limit a minimum number of characters that can be entered in a cell |