Limit maximum number of characters in a cell

This tutorial shows how to limit users to only be able to enter up to a specific number of characters in a cell using Excel or VBA

METHOD 1. Limit maximum number of characters in a cell using an Excel built-in option

EXCEL

Select a cell > Data tab > Data Tools group > Click on Data Validation > Data Validation > Select Settings tab > Select Text length > Select less than or equal to > Enter number > Click OK

1. Select a cell in which you want to limit users to only be able to enter a maximum number of characters.
Note: in this example we are selecting range B2.
Select a cell

2. Select the Data tab. Select the Data tab

3. Click on Data Validation in the Data Tools group.
4. Select Data Validation.
Click on Data Validation and select Data Validation

5. Select the Settings tab.
6. Select Text length in the Allow input box.
7. Select less than or equal to in the Data input box.
8. Enter the maximum number of characters you want a user to be able to enter in a selected cell.
9. Click OK
Note: in this example we are limiting a user to only be able to enter a maximum of five characters in a selected cell.
Select Settings tab, select Text length, select less than or equal to, enter a number and click OK

METHOD 2. Limit maximum 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 users to only be able to enter a maximum number of characters.
Note: in this example we are selecting range B2.
Select a cell

2. Select the Data tab. Select the Data tab

3. Click on Data Validation in the Data Tools group.
4. Select Data Validation.
Click on Data Validation and select Data Validation

5. Select the Settings tab.
6. Select Custom in the Allow input box.
7. Enter the formula =LEN(B2)<=5 in the Formula input box.
8. Click OK
Note: in this example we are limiting a user to only be able to enter a maximum of five characters in a selected cell. The selected cell being B2.
Select Settings tab, select Custom, enter formula and click OK

METHOD 1. Limit maximum number of characters in a cell

VBA

Sub Limit_maximum_number_of_characters()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B2")
'apply data validation
With Rng.Validation

.Add Type:=xlValidateTextLength, Operator:=xlLessEqual, Formula1:="5"

End With

End Sub

ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet in which you want to apply a limit of maximum number of characters that can be entered into a cell 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 limit of maximum number of characters that can be entered into it by changing the cell reference ("B2") in the VBA code.
Maximum Number of Characters: Select the maximum number of characters that can be entered in a cell by changing the number "5" in the VBA code.

Explanation about how to limit maximum number of characters in a cell

EXPLANATION

EXPLANATION

This tutorial shows how to apply a limit on the maximum number of characters that can be entered in a cell using Excel or VBA.
This tutorial provides two Excel methods that can be applied to limit the maximum number of characters that can be entered in a cell. The first method uses the Excel built-in data validation option (less than or equal to) and can be completed in nine 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 the maximum number of characters that can be entered in a cell.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to limit a minimum number of characters that can be entered in a cell
How to allow to only enter n number of characters in a cell