Limit Text Length in a Cell in Excel

You may have a requirement to limit the text length of cells in Excel. This could be to ensure that what is entered into the cell is exactly 9 characters, or is between 9 and 11 characters and so on.

Basic Validation of the Text Length

To limit the text length to a certain number of characters in Excel, we will use the Data Validation tool.

For this example, we would like to restrict the cell to exactly 9 characters to hold a Social Security Number or National Insurance Number.

1.  Select the column or range of cells you want to validate

2.  Click the Data tab on the Ribbon

3.  Click the Data Validation button in the Data Tools group

4.  Click the Allow: list arrow and select Text length

5.  Click the Data: list arrow and select equal to

6.  Type 9 in the Length: field

Limit text length to exactly 9 characters
Limit text length to exactly 9 characters

7.  Click the Ok button

Excel limits the text length of the data in the selected cells to 9 characters exactly.

Limit Text Length using Formulas

By using formulas you can create more advanced text length validation.

For example, we would like to restrict the cell to exactly 9 characters, but also ensure that no text characters are entered. This matches the format used for Social Security Numbers.

1.  Select the column or range of cells you want to validate. In this example column A has been selected

2.  Click the Data tab on the Ribbon

3.  Click the Data Validation button in the Data Tools group

4.  Click the Allow: list arrow and select Custom

5.  Type =AND(LEN(A1)=9,ISNUMBER(A1)) in the Formula box

Using formulas when validating text length
Using formulas when validating text length

The AND function is used to allow us to test more than one condition. The LEN function is used to ensure that exactly 9 characters are entered and the ISNUMBER function is used to ensure that no alphabetic characters are entered.

6. Click the Ok button

By using formulas in your data validation you can create some complex criteria that entries need to meet. By reducing inaccuracies in the data entered, you can ensure that your reports are more accurate and your figures correct.

Useful Links

Microsoft Excel training at Lynda.com

Online Microsoft Excel tutorials from the most respected authors on the market. Become an Excel guru by checking out this fantastic site.

Data Validation list tips

Tips on using Data Validation to create drop down lists in your spreadsheets.

More by this Author

  • Sort Pictures in an Excel List
    0

    If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...

  • Simulate a Cup Draw using Excel
    0

    Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...

  • Create an Interactive Excel Chart using Option Buttons
    6

    Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working